Data Manipulation Language Statements › Logical Record Clauses › WHERE Clause

WHERE Clause

Functions of the WHERE Clause

The WHERE clause has two major functions:

• To direct the program to a predefined path in 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 must be submitted to the DML precompiler before assembly.

Coding WHERE

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:

1. Comparisons enclosed in parentheses, in order of precedence within parentheses
2. Arithmetic, comparison, and Boolean operators in 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

Syntax

►►─── ,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 ───────────────────┘

Parameters

dba-designated-keyword/comparison

Specify selection criteria to be applied to the logical record.

dba-designated-keyword

Specifies 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-keyword can be no longer than 32 characters.

Note: A path must exist to service a request that includes dba-designated-keyword. If no such path exists, the DML precompiler issues an error message.

comparison

Specifies the comparison operation to be performed, using the indicated operands and operators. Comparison also may direct the logical record request to a path in the subschema.

Syntax for comparison contains 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-expression

Identifies a left or right comparison operand.

literal

Specifies an alphanumeric or numeric literal. Alphanumeric literals must be enclosed in site-standard quotation marks.

dd-defined-variable-field-name

Specifies a program variable storage field predefined in the dictionary.

logical-record-field-name

Specifies a data field that participates in the named logical record. Logical-record-field-name uniquely identifies the named logical-record field.

The optional OF LR entry 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-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 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/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 parameter must be alphanumeric values.

MATCHES

Is 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 is true if 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 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:

• @ matches any alphabetic character
• # matches any numeric character
• * matches any alphabetic or numeric character

Both the left operand and the mask must be alphanumeric values.

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.

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 calculate lrc-block-size, see @COPY IDMS.

Examples

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')))