Data Manipulation Language Statements › DC Status Codes › WHERE

WHERE

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 specific instructions for navigating the database.

  You need not be concerned about path selection; LRF automatically picks the most appropriate path to efficiently service the request.

• To specify selection criteria to be applied to a logical record. This allows the program to specify attributes of the desired logical record, thereby reducing the need for the program to inspect multiple logical records to isolate the logical record of interest.

The WHERE clause is issued in the form of a boolean expression that consists of comparisons and keywords connected by boolean operators (AND, OR, and NOT). The format of the WHERE clause follows COBOL syntax rules (that is, operands or operators are separated by a blank).

Syntax

►►─── WHERE ─┬───────┬───┬─ dba-designated-keyword ─┬─────────────────────────►
             └─ NOT ─┘   └─ comparison ─────────────┘

 ►─┬──────────────────────────────────────────────────────┬───────────────────►◄
   │ ┌──────────────────────────────────────────────────┐ │
   └─▼─┬─ AND ─┬─┬───────┬─┬─ dba-designated-keyword ─┬─┴─┘
       └─ OR ──┘ └─ NOT ─┘ └─ comparison ─────────────┘

Expansion of comparison

►─┬─ 'logical-record-field-name' ──────────────────────────────────┬─────────►
  ├─ idd-defined-variable-field-name ─┬───────────────────┬────────┤
  │                                   │ ┌───────────────┐ │        │
  │                                   └─▼─ OF group-id ─┴─┘        │
  ├─ logical-record-field-name ─┬───────────────────┬──┬─────────┬─┤
  │                             │ ┌───────────────┐ │  └─ OF LR ─┘ │
  │                             └─▼─ OF group-id ─┴─┘              │
  └─ arithmetic-expression ────────────────────────────────────────┘

►──┬── CONTAINS ─┬───────────────────────────────────────────────────────────►
   ├── MATCHES ──┤
   ├┬─ EQ ─┬─────┤
   │└─ = ──┘     │
   ├── NE ───────┤
   ├┬─ GT ─┬─────┤
   │└─ > ──┘     │
   ├┬─ LT ─┬─────┤
   │└─ < ──┘     │
   ├── GE ───────┤
   └── LE ───────┘

Parameters

dba-designated-keyword

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 precompiler flags this condition by issuing an error message.

comparison

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.

Parameters

logical-record-field-name

Specifies a data field that participates in the named logical record.

CONTAINS/MATCHES/EQ/NE/GT/LT/GE/LE

Specifies the comparison operator:

• CONTAINS— 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.
• MATCHES— 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.

  • EQ— Is true if the value of the left operand is equal to the value of the right operand.
  • NE— Is true if the value of the left operand is not equal to the value of the right operand.
  • GT— Is true if the value of the left operand is greater than the value of the right operand.
  • LT— Is true if the value of the left operand is less than the value of the right operand.
  • GE— Is true if the value of the left operand is greater than or equal to the value of the right operand.
  • LE— Is true if the value of the left operand is less than or equal to the value of the right operand.

'literal'

Any alphanumeric or numeric literal. Alphanumeric literals must be enclosed in quotation marks.

idd-defined-variable-field-name

The name of a program variable storage field predefined in the data dictionary.

OF

Uniquely identifies the named variable field.

This qualifier is required if idd-defined-variable-field-name is not unique within program variable storage.

A maximum of 15 different OF group-id qualifiers can be specified to identify as many as 15 levels of group elements.

group-id The name of the group element that contains the field.

logical-record-field-name

Specifies a data field that participates in the named logical record.

OF

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 not part of the logical record, and including all non-CA IDMS database records copied into the program.

A maximum of 15 different OF group-id qualifiers can be specified to identify as many as 15 levels of group elements.

group-id The name of the group element or database record that contains the field.

OF LR

Specifies that the value of the named field at the time 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 you do not specify OF LR, 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 processing.

arithmetic-expression

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.

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 PIC 9(3) USAGE DISPLAY, 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 on the COPY 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.

Operators in a WHERE clause are evaluated in the following order:

1. Comparisons enclosed in parentheses
2. Arithmetic, comparison, and boolean operators by order of precedence, from highest to lowest:
   1. Unary plus or minus in an arithmetic expression
   2. Multiplication or division in an arithmetic expression
   3. Addition or subtraction in an arithmetic expression
   4. MATCHES or CONTAINS comparison operators
   5. EQ, NE, GT, LT, GE, LE comparison operators
   6. NOT boolean operator
   7. AND boolean operator
   8. OR boolean operator
3. From left to right within operators of equal precedence

Examples

The following examples illustrate the use of the WHERE clause.

Example 1

The following logical-record request uses a DBA-designated keyword (PROGRAMMER-ANALYSTS) to direct LRF to a DBA-defined access path:

OBTAIN NEXT EMP-JOB-LR
  WHERE PROGRAMMER-ANALYSTS.

Example 2

The following logical-record request uses boolean selection criteria to specify the desired occurrence of EMP-JOB-LR:

OBTAIN EMP-JOB-LR
  WHERE OFFICE-CODE-0450 EQ '001'.