The WHERE clause has two major functions:
To direct the program to a path, predefined in the subschema by the DBA and transparent to the application program. This allows you to access the database without issuing words connected by boolean operators (AND, OR, and NOT). The format of the WHERE clause follows PL/I Syntax rules (that is, operands or operators are separated by a blank).
The WHERE clause is issued in the form of a boolean expression that consists of comparisons and kMSDMLP assumes the use of the 48-character set when it generates LRF code. For more information, see DML Precompiler Options.
►►─── WHERE ( ─┬───────────┬─┬─ dba-designated-keyword ─┬─────────────────────► └─┬─ NOT ─┬─┘ └─ comparison ─────────────┘ └─ ¬ ───┘ ┌─────────────────────────────────────────────────────────┐ ►─▼─┬─────────────────────────────────────────────────────┬─┴─ ) ────────────►◄ ├─┬─ AND ┌─┬─┬──────────┬─┬─ dba-designated-keyword ─┬┘ │ └─ & ──┘ │ └┬─ NOT ─┬─┘ └─ comparison ─────────────┘ └─┬─ OR ─┬─┘ └─ ¬ ───┘ └─ │ ──┘
Expansion of comparison
►►─┬─ literal ──────────────────────────────────────────────┬─┬─ CONTAINS ─┬──► │ ┌────────────┐ │ ├─ MATCHES ──┤ ├─▼─ group-id.─┴─ idd-defined-variable-field-name ───────┤ ├─┬─ EQ ─┬───┤ └─ arithmetic-expression ────────────────────────────────┘ │ └─ = ──┘ │ ├─┬─ NE ─┬───┤ │ └─ ¬= ─┘ │ ├─┬─ GT ─┬───┤ │ └─ > ──┘ │ ├─┬─ LT ─┬───┤ │ └─ < ──┘ │ ├─┬─ GE ─┬───┤ │ └─ >= ─┘ │ └─┬─ LE ─┬───┘ └─ <= ─┘ ►─┬─ literal ──────────────────────────────────────────────┬─────────────────►◄ │ ┌────────────┐ │ ├─▼─ group-id.─┴─ variable-field-name ───────────────────┤ │ ┌────────────┐ │ ├─▼─ group-id.─┴─ logical-record-field-name ─┬─────────┬─┤ │ └─ OF LR ─┘ │ └─ arithmetic-expression ────────────────────────────────┘
Specifies a DBA-designated keyword to be applied to the logical record that is the object of the command. Dba-designated-keyword is a keyword specified by the DBA that is applicable to the logical record named in the command; it can be no longer than 32 characters. The keyword represents an operation to be performed at the path level and serves only to route the logical-record request to the appropriate, predetermined path.
A path must exist to service a request that issues dba-designated-keyword. If no such path exists, the DML precompiler flags this condition by issuing an error message.
Specifies a comparison operation to be performed, using the indicated operands and operators. It also serves to direct the logical-record request to a path.
Individual comparisons and keywords 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.
Identifies a left or right comparison operand.
Specifies a literal value. Literal can be any alphanumeric or numeric literal. Alphanumeric literals must be enclosed in quotation marks.
Specifies a program variable storage field predefined in the dictionary. Idd-defined-variable-field-name must be an elementary element. It cannot be a group element. Group elements can only be used for qualification.
The optional qualifier group-id uniquely identifies the named variable field. This qualifier is required if idd-defined-variable-field-name is not unique within program variable storage. Group-id names the group element that contains the field. A maximum of 15 different group-id qualifiers can be specified to identify as many as 15 levels of group elements.
Specifies 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 add (+), subtract (-), multiply (*), and divide (/). Operands can be literals, variable-storage fields, and logical-record fields as described above. On the left side of the comparison you cannot use a key value.
Specifies the comparison operator. Operators are evaluated in the following order:
Is true if the value of the right operand occurs in the value of the left operand. Both operands included with the CONTAINS operator must be alphanumeric values and elementary elements.
Is true if each character in the left operand matches a corresponding character in the right operand (the mask). When MATCHES is specified, 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 is true if the end of the mask is reached before encountering a character in the left operand that does not match a corresponding character in the mask. The result is false if LRF encounters a character in the left operand that does not match a mask character.
Three special characters can be used in the mask to perform pattern matching: @, which matches any alphabetic character; #, which matches any numeric character; and *, which matches any alphabetic or numeric character. Both the left operand and the mask must be alphanumeric values and elementary elements.
Is true if the value of the left operand is equal to the value of the right operand.
Is true if the value of the left operand is not equal to the value of the right operand.
Is true if the value of the left operand is greater than the value of the right operand.
Is true if the value of the left operand is less than the value of the right operand.
Is true if the value of the left operand is greater than or equal to the value of the right operand.
Is true if the value of the left operand is less than or equal to the value of the right operand.
Specifies a data field that participates in the named logical record. Logical-record-field-name must be an elementary element. It cannot be a group element. Group elements can only be used for qualification.
The optional qualifier group-id uniquely identifies the named logical-record field. This qualifier is required if logical-record-field-name is not unique within all subschema records, including those that are not part of the logical record, and all non CA IDMS/DB records copied into the program. Group-id names the group element or database record that contains the field. A maximum of 15 different group-id qualifiers can be specified to identify as many as 15 levels of group elements.
The optional OF LR parameter specifies that the value of the named field at the time that the request is issued will be used throughout processing of the request. 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 if the field is required for further request processing.
Usage of the WHERE Clause
If the WHERE clause compares a CALC-key field to a literal, the literal's format must correspond exactly to the CALC-key definition. Enclose the literal in quotation marks if the CALC key has a usage of DISPLAY, and use leading zeros if the literal consists of fewer characters than the field's picture. For example, if the calc-key-field CALC key is defined as CHAR (3), code the WHERE clause as follows:
WHERE (calc-key-field) EQ '054';
The WHERE clause can contain as many comparisons and keywords as required to specify the criteria to be applied to the logical record. If necessary, the value of the SIZE parameter in the INCLUDE IDMS SUBSCHEMA_LR_CTRL statement can be increased to accommodate very large and complex WHERE clause specifications. Processing efficiency is not affected by the composition of the WHERE clause (other than the logical order of the operators, as noted below), since LRF automatically uses the most efficient path to process the logical-record request.
The following logical-record request uses a DBA-designated keyword (PROGRAMMER_ANALYSTS) to direct LRF to a DBA-defined access path:
OBTAIN NEXT RECORD (EMP_JOB_LR) WHERE (PROGRAMMER_ANALYSTS);
The following logical-record request uses boolean selection criteria to specify the desired occurrence of EMP_JOB_LR:
OBTAIN RECORD (EMP_JOB_LR) WHERE (OFFICE_CODE_0450 EQ '001');
|
Copyright © 2014 CA.
All rights reserved.
|
|