Skip to main content
Previous sectionNext section

WHEN clause

Specifies a condition for query results.

Description

The WHEN clause is in most cases a synonym for the WITH clause. Refer to the WITH clause for details about condition expressions.

The WHEN clause differs from the WITH clause in the following respects:

  • WHEN has no complement (inverse) keyword. WITH has the complement keywords WITH NO and WITHOUT.

  • WHEN can be used to specify a conditional expression for LISTF, LISTPA, LISTPH, and LISTS. WITH cannot be used with these commands.

  • WHEN requires single quote delimiters for test values. WITH supports single quote, double quote, and backslash delimiters. WHEN requires delimiters for a LIKE clause (WHEN @ID LIKE 'A...'); these delimiters are optional in a WITH clause (WITH @ID LIKE A...).

  • Multiple WITH clauses must be associated with explicit OR or AND logical operators. WHEN clauses are specified without explicit OR or AND logical operators; the default is implicit AND. When specifying multiple WHEN clauses, or a WITH clause with one or more WHEN clauses, implicit AND logic is applied. You can associate two WHEN clauses with an explicit OR, but not a WITH clause and a WHEN clause. An explicit OR between a WITH clause and a WHEN clause is ignored; the two clauses are associated with an implicit AND. The following are valid syntactical forms that return the same results:

    WHEN <condition1> WHEN <condition2>
    WHEN <condition1> AND WHEN <condition2>
    WITH <condition1> WHEN <condition2>
    WITH <condition1> AND WHEN <condition2>
    WITH <condition1> OR WHEN <condition2> (avoid using this) 
    
  • WHEN can be used with the ASSOCIATED keyword, associating multiple conditional clauses using exclusive OR logic. A WHEN ASSOCIATED clause can have either of the following syntax:

    WHEN ASSOCIATED (condition) OR WHEN condition
    
    WHEN ASSOCIATED (condition OR condition)
    

    Note that the parentheses are mandatory. These parentheses may enclose a single conditional expression or multiple conditional expressions. ASD is a synonym for ASSOCIATED. The ASSOCIATED keyword cannot be use with the WITH keyword.

WHEN ASSOCIATED

The WHEN ASSOCIATED clause permits a more sophisticated use of compound conditions, using XOR (exclusive OR) logic. The CMQL default is inclusive OR logic.

The following example uses inclusive OR logic:

LIST VOC F1 F4 WITH (F1="V" AND F4 # "") OR WITH @ID > "W"
Copy code to clipboard

returns “25 Items listed.” and displays a list of 25 items:

VOC.........  F1............. F4.............
 
BSELECT       V               L
COUNT.VERB    V               L
LIST          V               DA
LIST.ITEM     V               I
LIST.LABEL    V               D
PRINT.CATALOG V
REFORMAT      V               DA
SELECT        V               L
SORT          V               DAS
SORT.ITEM     V               IS
SORT.LABEL    V               DS
SREFORMAT     V               DAS
SSELECT       V               LS
STAT          V               J
SUM.VERB      V               T
WHEN          K
WHERE         K
WHERE.VERB    V
WHO           V
WITH          K
WITHIN        K
WITHOUT       K
Z             V
ZH            V
[             V

25 Items listed.

This example creates a result set by including or excluding items that meet each conditional expression.

The following WHEN ASSOCIATED statement uses exclusive OR (XOR) logic.

LIST VOC F1 F4 WHEN ASD (F1="V" AND F4 # "") OR WHEN @ID > "W"
Copy code to clipboard

The result set is selected using exclusive OR logic, but the count of items listed uses inclusive OR logic. Therefore, it returns “25 Items listed.” and displays a list of 20 items:

VOC.........  F1............. F4.............
 
BSELECT       V               L
COUNT.VERB    V               L
LIST          V               DA
LIST.ITEM     V               I
LIST.LABEL    V               D
PRINT.CATALOG V
REFORMAT      V               DA
SELECT        V               L
SORT          V               DAS
SORT.ITEM     V               IS
SORT.LABEL    V               DS
SREFORMAT     V               DAS
SSELECT       V               LS
STAT          V               J
SUM.VERB      V               T
WHERE.VERB    V
WHO           V
Z             V
ZH            V
[             V
 
25 Items listed.

This example creates a result set of all records with F1="V", then removes items that don’t meet the other conditional expression criteria from this result set.