WITH Clauses (DQL)

Use WITH clauses to apply selection criteria to the selection of rows for the active found set.
datacom
Use WITH clauses to apply selection criteria to the selection of rows for the active found set. WITH clauses can be added to FIND statements and/or relationship clauses to qualify rows from any table read by the query for selection. Their use can improve performance. A WITH clause contains selection criteria stating the characteristics a row must have to qualify for selection. The selection criteria are expressed as one or more logical expressions that
Dataquery
evaluates during the selection process. See Multiple WITH Clauses for more information about multiple WITH clauses.
WITH clauses can use the following data types: CHAR, NUM, DEC, BIN, 1/2 BIN, FUL BIN, DBL CHAR, KANJI.
Topics discussed on this page include:
Syntax Diagrams
The following diagrams show the use of a WITH clause in a FIND statement and in a RELATED BY clause.
►►─ FIND ─┬─────────┬─
table-name
─┬────────────┬─┬────────┬─┬────────┬───────► └─ COUNT ─┘ └─ (
status
) ─┘ └─ ROWS ─┘ └─ WITH ─┘ ►─
operand1
comparison-operator
operand2
─┬───────────┬──────────────────►◄ └─┬─ AND ─┬─┘ └─ OR ──┘
Additional comparison expressions may be added using AND and OR. For example:
  • FIND all personnel rows
  • WITH name-column = smith
  • AND city-column = dallas
  • OR city-column = houston
►►─ FIND ─┬─────────┬─
table-name
─┬────────────┬─┬────────┬─ RELATED ────────► └─ COUNT ─┘ └─ (
status
) ─┘ └─ ROWS ─┘ ►─┬──────┬─
key-name
─┬───────┬─┬──────┬─┬─────────┬─
table-name
────────────► └─ BY ─┘ └─ KEY ─┘ └─ TO ─┘ └─ FIRST ─┘ ►─┬────────────┬─┬───────┬─┬────────┬─
operand1
comparison-operator
───────► └─ (
status
) ─┘ └─ ROW ─┘ └─ WITH ─┘ ►─
operand2
─┬───────────┬───────────────────────────────────────────────────►◄ └─┬─ AND ─┬─┘ └─ OR ──┘
Additional comparison expressions may be added using AND and OR.
For example:
  • FIND all personnel ROWS
  • WITH dept-column = sales
  • RELATED BY social-security-column TO payroll
  • WITH payroll street-column NOT CONTAINING 'p.o.'
  • OR 'box'
A series of values following an EQ (=) or NE (,=) cannot be separated with AND or OR. For example:
Right:
WITH FIELDA = 'TX' 'CA' 'NY' or WITH FIELDA NE 'TX' 'CA' 'NY'
Wrong:
WITH FIELDA NE 'TX' and 'CA' and 'NY'
Only the keywords for the WITH clause are described in the following sections. See FIND Statement (DQL) for a description of the FIND statement and Relationship Clauses (DQL) for a description of relationship clauses.
Further information appears in Multiple WITH Clauses and Evaluation of Logical Expressions.
WITH Clause Operand1
►►─┬────────┬─
operand1
comparison-operator
operand2
─────────────────────►◄ └─ WITH ─┘
Operand1
in the WITH clause diagram represents any valid column or key contained in the specified table or an arithmetic expression enclosed in parentheses.
Dataquery
evaluates the specified name as a column first, then as a key if no column with that name is found.
Dataquery
compares the column, key or arithmetic expression to the value of operand2. See Evaluation of Arithmetic Expressions for more information about how
Dataquery
processes these expressions. In comparing columns,
Dataquery
compares data types. In comparing keys,
Dataquery
only performs logical compares.
Columns and Keys
Operand1 can be any valid column or key, including a character column, a numeric column, a key, or a repeating field.
Key Name:
If either operand1 or operand2 is a key and the other is a column, the key can contain only one column, but it must be a character column.
Repeating Fields:
A column can be one occurrence within a repeating field. If that is the case, use subscripts to identify which occurrence is to be used. A typical subscript looks like this:
SALES-RESULTS (5,2)
Table Names:
When operand1 and operand2 represent values in two different tables, prefix one or both with table names and use a period between the table name and column name. Do not use a table name if operand1 is an arithmetic expression.
Numeric Column:
If operand1 of a WITH clause is a single numeric column, its comparison with operand2 is based on the decimal precision of operand1.
Arithmetic Expressions
If you are using an arithmetic expression to represent operand1, you must enclose the expression within parentheses. In addition, you can have multiple expressions within the parentheses. However, an arithmetic expression cannot contain any mathematical functions (MAX, AVG, and so forth).
The following is a sample WITH clause with operand1 as an arithmetic expression.
►►─┬────────┬─
operand1
comparison-operator
operand2
─────────────────────►◄ └─ WITH ─┘
If operand1 is an arithmetic expression, such as (FLD/A + FLD/B) the comparison is based on the precision of the object after the number is rounded. For example, assume that the value of FLD/A is .41 and the value of FLD/B is 0. If the WITH clause is
WITH FLD/A = 0.00
the WITH clause evaluates false. If the WITH clause is
WITH (FLD/A + FLD/B) = 0
the WITH clause evaluates true, because the .41 result of operand1 expression rounds to 0. If the object were specified as 0.00, no row would be found. Rounding is not required and 0.41 does not equal 0.00.
WITH Clause Comparison Operators
►►─┬────────┬─
operand1
comparison-operator
operand2
─────────────────────►◄ └─ WITH ─┘
Explanation
Comparison-operator
in the preceding diagram indicates the type of comparison to be performed between operand1 and operand2. The following chart lists the operators you can use in a WITH clause, shows alternate entries (if they exist), and gives examples on how to use the operators.
Operator
Alternate Entry
Explanation/Example
EQUAL
EQ, =
Dataquery
selects only those rows for which the value of operand1 equals the value of operand2.
WITH STATE EQUAL 'TX'
In addition, you can use THRU with the EQUAL operation to specify a range of values for a numeric column.
WITH YTD-SALES = 1000 THRU 2000
You can include a series of values for operand2 in a list fashion using the operator EQUAL.
WITH STATE = 'TX','NY', 'CA'
NOT EQUAL
NE, ,=
Dataquery
selects only those rows for which operand1 is not equal to the value of operand2.
WITH STATE NE 'TX','NY', 'CA'
GREATER THAN
GT, >
Dataquery
selects only those rows for which the value of operand1 is greater than the value of operand2
WITH YTD-SALES GT 1000
GREATER THAN OR EQUAL TO
GTE
Dataquery
selects only those rows for which the value of operand1 is greater than or equal to the value of operand2.
WITH YTD-SALES GTE 1000
LESS THAN
LT, <
Dataquery
selects only those rows for which the value of operand1 is less than the value of operand2.
WITH YTD-SALES LT 1000
LESS THAN OR EQUAL TO
LTE
Dataquery
selects only those rows for which the value of operand1 is less than or equal to the value of operand2.
WITH YTD-SALES LTE 1000
CONTAINING
(none)
Dataquery
selects only those rows for which the value of operand1 contains the value of operand2 in any position of operand1. The operand2 value must be a literal. This instructs
Dataquery
to select only those rows which contain the literal.
WITH CITY CONTAINING 'DA'
NOT
,
Dataquery
selects all rows for which the value of operand1 does not compare with value for operand2. You can use NOT with any of the operators listed preceding except NOT EQUAL.
WITH YTD-SALES NOT LT 1000
WITH Clause Operand2
►►─┬────────┬─
operand1
comparison-operator
operand2
─────────────────────►◄ └─ WITH ─┘
Explanation
Use one of the following as
Operand2
of a WITH clause, depending on its compatibility with operand1 or with a particular operator.
  • A
    Datacom Datadictionary
    name of a column in the last table named
  • A
    Datacom Datadictionary
    name of a key in the last table named
  • A literal value
  • The word
    null
    (meaning
    empty
    )
See the following pages for explanations of these operand2 options.
Dataquery
evaluates the name specified for operand2 as a key first, then as a column if no key is found with that name. If a key or column is named,
Dataquery
compares the contents of the column to operand1. If a literal value or
null
is specified, that value is compared to operand1.
The value of the column, key, or literal must be structured the same as the value for operand1.
Do not use the operator CONTAINING if operand2 is a column or key.
Columns and Keys
See Columns and Keys for detailed information about columns and keys in a WITH clause.
Literal Value
If operand1 is a character column,
the literal value for operand2 can contain any alphabetic or numeric character. If the literal value is not a number, it must be enclosed within apostrophes ('). The length of the literal cannot exceed the length of operand1. If the length of the literal is smaller,
Dataquery
adjusts the value to make the length of the literal the same as operand1 by padding with blanks to the right, the same as operand1.
If operand1 is a numeric column,
the literal must contain numeric characters, which do not exceed the length of operand1, and a decimal point. The literal should not be enclosed within apostrophes ('). You can indicate a negative value by prefixing the literal with a minus sign (-).
If operand1 is a single-column key,
the literal must conform to the structure of the column in the key and must be enclosed within apostrophes (').
Also, you can use
literal masking
to compare only portions of operand1 and operand2 to each other.
If operand1 is a multi-column or compound key,
you can specify a literal value for each key column; however, if you specify only one value,
Dataquery
compares the literal value to the high-order column in the key. If you do specify a literal value for each key column, delimiters must separate each value. The entire literal must be enclosed within apostrophes ('). The following is a sample diagram for a literal for a multi-column key.
A delimiter can be any character except a blank, the literal masking character, a dollar sign ($), a hyphen (-), the decimal point character, an apostrophe ('), a letter (a-z), a number from 0 to 9, the Kanji shift in character, or the Kanji shift out character.
'/lit1/lit2/lit3/'
Literal Masking
You can use a literal value which specifies that only certain positions within a column are to be compared. This is called literal masking. A literal mask designates a set of characters which
Dataquery
compares position by position to the value of operand1. In those positions which you do not want to specify a character, use the literal masking character.
If the column is numeric, only numeric characters other than the literal masking character can be specified in the literal. Unsigned, zoned, decimal columns and signed or unsigned, packed decimal columns can be masked. Do not include decimal points, dollar signs or minus signs in the literal. You can specify up to 15 literal values per WITH clause.
Select the System Profile topic from the List of Help Topics panel (HELP) command to see which character has been designated as the Literal Masking character at your site. The default is the pound sign (#).
For example, if operand1 is a five-character column, and you want to select all the rows which have a C in the second position of its value, you can specify the following literal mask:
'#C#'
In this example, the pound sign is a literal masking character.
Notice that the literal does not contain five characters. Whenever the (#) appears as the last position of the mask,
Dataquery
assumes the special character fills the other positions to the proper length of the column.
Multiple WITH Clauses
A WITH clause can contain multiple logical expressions joined by AND or OR.
Dataquery
selects the data meeting the conditions stated in each expression of the WITH clause.
If expressions within the WITH clause are joined by AND,
Dataquery
retrieves data which matches all of the stated expressions. If expressions within the WITH clause are joined by OR,
Dataquery
retrieves data which matches at least one of the stated expressions.
Example
The following example illustrates the use of AND in a WITH clause.
FIND 10 CAI-CUST-REC ROWS WITH STATE = 'TX' AND CITY = 'DALLAS' AND PH-EXCH = '248'
The result of this FIND statement is a set of 10 customer rows for customers located in Dallas, Texas, and within the 248 phone exchange.
Example
In the example below, a WITH clause contains expressions joined by OR.
FIND ALL CAI-CUST-REC ROWS WITH CITY = 'DALLAS' OR AREA-CD = '214'
When you execute this FIND statement,
Dataquery
selects all the customer rows for Dallas or customers who are located within the area code 214.
You can group logical expressions together within parentheses to change the standard order of evaluation.
Dataquery
evaluates expressions within parentheses before any other expressions. You can specify up to five levels of parentheses in a FIND statement.
If NOT precedes an entire WITH clause contained within parentheses, it reverses the meaning of the operators within the parentheses. However, if more WITH clauses follow the parentheses, the NOT operator does not negate them.
Example
The following two examples illustrate the use of NOT. In the first example,
Dataquery
selects those customer rows which do not have a state address of TX, NY or CA.
FIND 10 CAI-CUST-REC ROWS WITH STATE NOT ='TX' 'NY' 'CA'
In the example below,
Dataquery
selects those personnel rows which do not have a state address of TX, NY or CA, but do have a city address which contains DA.
FIND 10 CAI-CUST-REC ROWS WITH STATE NOT = 'TX ' 'NY' 'CA' AND CITY CONTAINING 'DA'
Evaluation of Logical Expressions
Dataquery
evaluates logical expressions in the following order:
Expressions contained within parentheses
  1. Expressions within parentheses preceded by NOT
  2. Expressions joined with AND
  3. Expressions joined with OR
The following chart illustrates how
Dataquery
evaluates logical expressions which contain parentheses or NOT.
Example and Explanation
EXPR1
AND
EXPR2
AND
EXPR3
Dataquery
selects only those rows which meet the criteria specified in all three expressions.
EXPR1
AND
(EXPR2 OR EXPR3)
Dataquery
selects only those rows which meet the criteria in EXPR1 and the criteria in either EXPR2 or EXPR3.
(EXPR1 AND EXPR2)
OR
EXPR3
Dataquery
selects only those rows which meet the criteria stated in both EXPR1 and EXPR2 or just the criteria stated in EXPR3.
NOT
(EXPR1 AND EXPR2)
AND
EXPR3
Dataquery
selects only those rows which do not meet the criteria specified in either EXPR1 or EXPR2, but do meet the criteria in EXPR3.
If you do not use parentheses to group your logical expressions,
Dataquery
will evaluate expressions joined by AND first. For example,
EXPR1 AND EXPR2 OR EXPR3
is equivalent to
(EXPR1 AND EXPR2) OR EXPR3
WITH Limitations
Following are important limitations to observe in constructing WITH clauses.
  • Two columns or keys that are not the same type cannot be compared.
  • Columns that are greater than 240 characters in length cannot be used in a WITH clause or in a SET statement in an INSERT or UPDATE query.
  • The maximum number of literal values per WITH statement is 15.
  • Columns used in a WITH clause as part of an arithmetic expression cannot be qualified.
  • The maximum number of OR entries in a WITH statement is 15. (That is, a maximum of 14 ORs are allowed.)
  • Values following an EQ (=) or NE (,=) cannot be separated by AND or OR.
  • SQL DATE, TIME and TIMESTAMP cannot be used with DQL SET, SORT and WITH.
  • A numeric column whose value can be zero should not be used as a divisor in a SET statement or an arithmetic expression.
  • The maximum numeric precision is 18 digits.
  • An arithmetic expression cannot have more than five levels of nested parentheses.