General DDDL Syntax Options › Displaying Entity Occurrences › WHERE Clause (Conditional Expressions)

WHERE Clause (Conditional Expressions)

The WHERE clause of a DISPLAY/PUNCH ALL statement defines a condition. The outcome of a test for the condition determines which occurrences of the named entity type the DDDL compiler selects for display.

The WHERE clause can contain a single condition, or two or more conditions combined with the logical operators AND or OR. The logical operator NOT specifies the opposite of the condition. The DDDL compiler evaluates operators in a WHERE clause one at a time, from left to right, in order of precedence. The default order of precedence is as follows:

• MATCHES or CONTAINS keywords
• EQ, NE, GT, LT, GE, LE operators
• NOT
• AND
• OR

If parentheses are used to override the default order of precedence, the DDDL compiler evaluates the expression within the innermost parentheses first.

Syntax: WHERE Clause (for conditional expressions)

►►─── WHEre conditional-expression ───────────────────────────────────────────►◄

Expansion of conditional-expression

►►─┬─ mask-comparison ─────────────────────────┬──────────────────────────────►
   ├─ value-comparison ────────────────────────┤
   └─┬───────┬─ ( ─┬────────────────────┬─ ) ──┘
     └─ NOT ─┘     ├─ mask-comparison ──┤
                   └─ value-comparison ─┘

 ►─┬────────────────────────────────────────────────────────────┬─────────────►◄
   │ ┌────────────────────────────────────────────────────────┐ │
   └─▼─┬─ AND ─┬─┬─ mask-comparison ────────────────────────┬─┴─┘
       └─ OR ──┘ ├─ value-comparison ───────────────────────┤
                 └─┬───────┬─ ( ─┬─ mask-comparison ──┬─ ) ─┘
                   └─ NOT ─┘     └─ value-comparison ─┘

Expansion of value-comparison

►►─┬─ entity-option ───────────────┬──────────────────────────────────────────►
   ├─ numeric-literal ─────────────┤
   └─ 'character-string-literal' ──┘

 ►─┬─ IS ─┬───────┬─────┬─┬─ entity-option ──────────────┬────────────────────►◄
   │      └─ NOT ─┘     │ ├─ numeric-literal ────────────┤
   ├─ NE ───────────────┤ └─ 'character-string-literal' ─┘
   └─┬───────┬─┬─ EQ ─┬─┘
     └─ NOT ─┘ ├─ =  ─┤
               ├─ GT ─┤
               ├─ ◄  ─┤
               ├─ LT ─┤
               ├─ <  ─┤
               ├─ GE ─┤
               └─ LE ─┘

Expansion of mask-comparison

►►─── entity-option ──┬─ CONTAINS ─┬─ 'mask-value' ───────────────────────────►◄
                      └─ MATCHES ──┘

Parameters

NOT

Specifies that the opposite of the condition fulfills the test requirements; if NOT is specified, the condition must be enclosed in parentheses.

AND

Specifies a logical operator to accompany multiple conditions. The expression is true only if the outcome of both test conditions is true.

OR

Specifies a logical operator to accompany multiple conditions. The expression is true if the outcome of either one or both test conditions is true.

value-comparison

Compares values represented in the left and right-side operands based on the specified comparison operator.

entity-option

Identifies a syntax option associated with the named entity type; valid options for each entity type are listed in the table following these parameter descriptions.

numeric literal

Identifies a numeric value.

'character-string-literal'

Identifies a character string enclosed in quotes.

IS/NOT

Specifies whether the left operand is equal (IS) or is not equal (IS NOT) to the right operand.

NE

Specifies whether the left operand is not equal to the right operand.

EQ/GT/LT/GE/LE

Specifies whether the left operand is equal to, greater than, less than, greater than or equal to, or less than or equal to the right operand. Each operator can be preceded by NOT to specify the opposite of the condition.

mask-comparison

Compares an entity type operand with a mask value.

CONTAINS

Searches the left operand for an occurrence of the right operand. The length of the right operand must be less than or equal to the length of the left operand. If the right operand is not contained entirely in the left operand, the outcome of the condition is false.

MATCHES

Compares the left operand with the right operand one character at a time, beginning with the leftmost character in each operand. When a character in the left operand does not match a character in the right operand, the outcome of the condition is false.

'mask-value'

Identifies the right operand as a character string; the specified value must be enclosed in quotation marks. The following characters can be specified in mask-value:

• @ matches any alphabetic character in entity-option
• # matches any numeric character in entity-option
• * matches any character in entity-option

Valid Entity Options for the WHERE Clause

Date Selection Criteria

In the following WHERE clause options, you can select the date as a value-comparison string in the form 'MM/DD/YY' on the right-hand side of the conditional expression:

• DATE CREATED
• DATE LAST UPDATED
• DATE COMPILED

The extraction interprets the date in CCMMDDYY form to accurately determine the relationship of dates. For example, the following DISPLAY ALL statement specifies the search criteria to identify the RECORD occurrences whose DATE CREATED values (which are also evaluated in CCYYMMDD form) are greater than the specified string:

display all records where
    date created ◄ '01/01/96'.

The DISPLAY ALL process determines that the date '01/01/96' is greater than the date '12/31/95'.

Alternatively, you can specify the value-comparison string on either side of the conditional expression in the form 'CCYYMMDD' to achieve the same results.

You can substitute day, month, or year for each of the WHERE clause options. For example, the following DISPLAY ALL statement specifies a search condition which is based on month and year:

display all records where
    month created = '01' and
    year created ◄ '95'.