This article describes the following information:
This article describes the following information:
Functions of the WHERE Clause
The WHERE clause has two major functions:
- To direct the program to a predefined pathin the subschema. The path is defined by the DBA and is transparent to the application program. Predefined paths allow the program to access database records without issuing specific instructions for navigating the database.
- To specify selection criteria to be applied to a logical record. Selection criteria allow the program to specify attributes of the desired logical record, reducing the need for the program to inspect multiple logical record occurrences.
Two Elements in a WHERE Clause
The WHERE clause is constructed from two elements:
- A positional parameter that contains the key value WHERE
- An Assembler remark that encodes a Boolean expression that consists of comparisons and keywords connected by Boolean operators (AND, OR, and NOT)
An Assembler logical record DML statement that contains a WHERE clause consists of an Assembler macro parameter concatenated with a compiler-level expression. The remark is resolved by the DML precompiler, not by the assembler. Therefore, programs that contain logical record DML statements using WHERE clauses
mustbe submitted to the DML precompiler before assembly.
Because the Boolean expression is treated as an Assembler remark, it can be written in a more readable form than conventional Assembler statements. WHERE clauses can span several lines in an Assembler program. The keyword WHERE must begin in column 16, continuation lines must be in column 16 or greater, and are marked by coding a nonblank character in column 72. Descriptive comments cannot be on the same line as the WHERE clause.
Including Boolean Operators
Individual comparisons and keywords must be connected by the Boolean operators AND, OR, and NOT. Parentheses can be used to clarify a multiple-comparison Boolean expression or to override preceding operators.
Operators in a WHERE clause are evaluated in the following order:
- Comparisons enclosed in parentheses, in order of precedence within parentheses
- Arithmetic, comparison, and Boolean operators in order of precedence, from highest to lowest:
- Unary plus or minus in an arithmetic expression
- Multiplication or division in an arithmetic expression
- Addition or subtraction in an arithmetic expression
- MATCHES or CONTAINS comparison operators
- EQ, NE, GT, LT, GE, LE comparison operators
- NOT Boolean operator
- AND Boolean operator
- OR Boolean operator
- From left to right within operators of equal precedence
►►─── ,WHERE ─┬───────┬─┬─designated-keyword─┬──────────────────────────────► └─ NOT ─┘ └─comparison─────────┘ ►─┬────────────────────────────────────────────────────┬─────────────────────►◄ │ ┌────────────────────────────────────────────────┐ │ └─▼──┬─ AND ─┬──┬───────┬─┬─designated-keyword─┬─┴─┘ └─ OR ──┘ └─ NOT ─┘ └─comparison─────────┘Expansion of comparison►►─┬─literal─────────────────────────────────┬──────────────────────────────► ├─idd-defined-variable-field-name─────────┤ ├─logical-record-field-name─┬─────────┬───┤ │ └─ OF LR ─┘ │ └─arithmetic-expression───────────────────┘ ►──┬── CONTAINS ─┬───────────────────────────────────────────────────────────► ├── MATCHES ──┤ ├┬─ EQ ─┬─────┤ │└─ = ──┘ │ ├── NE ───────┤ ├┬─ GT ─┬─────┤ │└─ > ─┘ │ ├┬─ LT ─┬─────┤ │└─ < ─┘ │ ├── GE ───────┤ └── LE ───────┘ ►─┬─literal─────────────────────────────────┬──────────────────────────────►◄ ├─idd-defined-variable-field-name─────────┤ ├─logical-record-field-name─┬─────────┬───┤ │ └─ OF LR ─┘ │ └─arithmetic-expression───────────────────┘
- dba-designated-keyword/comparisonSpecify selection criteria to be applied to the logical record.
- dba-designated-keywordSpecifies a keyword that applies to the named logical record. The DBA has previously associated this keyword with the named logical record; the keyword routes the logical-record request to the appropriate predetermined path in the subschema.Dba-designated-keywordcan be no longer than 32 characters.A path must exist to service a request that includesdba-designated-keyword. If no such path exists, the DML precompiler issues an error message.
- comparisonSpecifies the comparison operation to be performed, using the indicated operands and operators.Comparisonalso may direct the logical record request to a path in the subschema.Syntax forcomparisoncontains individual comparisons and keywords that are connected by the Boolean operators AND, OR, and NOT. Parentheses can be used to clarify a multiple-comparison Boolean expression or to override the precedence of operators.
- literal/idd-defined-variable-field-name/ logical-record-field-name/arithmetic-expressionIdentifies a left or right comparison operand.
- literalSpecifies an alphanumeric or numeric literal. Alphanumeric literals must be enclosed in site-standard quotation marks.
- dd-defined-variable-field-nameSpecifies a program variable storage field predefined in the dictionary.
- logical-record-field-nameSpecifies a data field that participates in the named logical record.Logical-record-field-nameuniquely identifies the named logical-record field.The optionalOF LRentry specifies that the value of the named field at the time the request is issued will be used throughout request processing. If the value of the field changes during request processing, LRF will continue to use the original value. If the OF LR entry is not included and the value of the field changes during request processing, the new field value in variable storage will be used.
- arithmetic-expressionSpecifies an arithmetic expression designated as a unary minus (-), unary plus (+), simple arithmetic operation, or compound arithmetic operation. Arithmetic operators permitted in an arithmetic expression are plus (+), minus (-), an asterisk (*), and a slash (/). These arithmetic operators must have a blank on either side. Operands can be the literals, variable fields, or the logical-record fields described above.
- CONTAINS/MATCHES/EQ/NE/GT/LT/GE/LESpecifies the comparison operator.
- CONTAINSIs true if the value of the right operand occurs in the value of the left operand. Both operands included with the CONTAINS parameter must be alphanumeric values.
- MATCHESIs true if each character in the left operand matches a corresponding character in the right operand (the mask). LRF compares the left operand with the mask, one character at a time, moving from left to right.The result of the match is either true or false:
- The result istrueif LRF reaches the end of the mask before encountering a character in the left operand that does not match a corresponding mask character.
- The result isfalseif LRF encounters a character in the left operand that does not match a mask character.
- @matches any alphabetic character
- #matches any numeric character
- *matches any alphabetic or numeric character
- EQIs true if the value of the left operand is equal to the value of the right operand.
- NEIs true if the value of the left operand is not equal to the value of the right operand.
- GTIs true if the value of the left operand is greater than the value of the right operand.
- LTIs true if the value of the left operand is less than the value of the right operand.
- GEIs true if the value of the left operand is greater than or equal to the value of the right operand.
- LEIs true if the value of the left operand is less than or equal to the value of the right operand.The WHERE clause can contain as many comparisons and keywords as are required to specify the criteria you want. Processing efficiency is not affected by the composition of the WHERE clause (other than the logical order of the operators), since LRF automatically uses the most efficient path to process the logical-record request.If necessary, the value of the SIZE parameter on the @COPY IDMS,SUBSCHEMA-LR-CTRL, @SSLRCTL, and @BIND SUBSCH statements can be increased to accommodate very large and complex WHERE clause specifications. For the algorithm to calculatelrc-block-size,see @COPY IDMS.
The WHERE clause shown below uses Boolean selection criteria to obtain the requested EMPJOBLR occurrence. This statement retrieves any customer in Massachusetts who has an outstanding balance greater than $1500, or who has an outstanding balance less than $500 and has a questionable credit rating.
@OBTAIN EMPJOBLR WHERE MASSACHUSETTS AND ((UNITS * PRICE) - * PAYMENT GT 1500 OR ((UNITS * PRICE) - * PAYMENT GT 500 AND (CREDRATE * EQ 'REF' OR CREDRATE EQ 'REJ')))