WHERE Clause

This article describes the following information:
idmscu19
This article describes the following information:
2
2
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.
    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')))