WITH clause

Synonym and Complement Keywords

The WITH keyword has the following synonyms: IF, WHERE, and WHEN. (WHEN is not an exact synonym. It differs from WITH in a few circumstances, and supports features not supported by WITH.)

The complement (inverse) of the WITH keyword is WITHOUT. The WITHOUT keyword has the following synonyms: WITH NO, WITH NOT; IF NO, IF NOT; WHERE NO, WHERE NOT. (WHEN NO and WHEN NOT are not valid keyword phrases.)

For example, the following are all equivalent statements:

LIST VOC F1 WITHOUT F1="K" AND WITHOUT F1="V"
LIST VOC F1 WITHOUT F1="K" AND WITH NO F1="V"
LIST VOC F1 WITHOUT F1="K" AND NO F1="V"
LIST VOC F1 WITH NO F1="K" AND NO F1="V"
LIST VOC F1 IF NO F1="K" AND NO F1="V"
LIST VOC F1 WHERE NO F1="K" AND NO F1="V"

Note that the complement keyword affects only the condition expression that immediately follows it. Subsequent conditions default to WITH. Thus the following example returns all records where F1 is not K:

LIST VOC F1 WITHOUT F1="K" OR F1="V"

You can use parentheses to extend the scope of a complement keyword to multiple conditions. Thus the following example returns all records where F1 is neither K nor V:

LIST VOC F1 WITHOUT (F1="K" OR F1="V")

Synonym and Complement Keywords

The LIKE keyword operator selects values that match the pattern code. The MATCHES keyword operator, and the MATCHING keyword operator are synonyms.

The UNLIKE keyword operator reverses the sense of the pattern string. Thus LIKE "3A" or MATCHES "3A" returns three-letter words; UNLIKE "3A" returns all values except three-letter words. The NOT.MATCHING keyword operator is a synonym.

Pattern Match Codes

The following are the available pattern match codes:

Any of the following combinations of nested quote marks can be used: "patcode'literal'", 'patcode"literal"', \patcode'literal'\, \patcode"literal"\. When specifying a literal with an ellipsis, quote marks are optional.

A pattern may consist of only pattern match codes, with no literals. For example, the pattern "2A" matches all two-letter words.

Pattern Match Examples

The following example searches the @ID dictionary entry field of the VOC for all values that begin with the letter A. The LIST command lists those items that pass this condition test:

LIST VOC WITH @ID LIKE A...

It lists items such as: A ABORT AFTER ALL ALL.MATCH AN AND ANY ARE AS ASC ASD ASSIGN ASSOC ASSOC.WITH ASSOCIATED ASSOCIATION AT ATTACH.ACCOUNTS AUTOLOGOUT AVERAGE AVG. Note that item A passes this test, with the ... representing no trailing characters.

The following example searches the @ID dictionary entry field of the VOC for all values that end with the letter A. The LIST command lists those items that pass this condition test:

LIST VOC WITH @ID LIKE ...A

It lists items such as: A CLEARDATA DATA LISTPA. Note that item A passes this test, with the ... representing no leading characters.

The following example searches the @ID dictionary entry field of the VOC for all values that contain the substring SS, including at the beginning and at the end of the @ID value. The LIST command lists those items that pass this condition test:

LIST VOC WITH @ID LIKE ...SS...

It lists items such as: ASSIGN ASSOC ASSOC.WITH ASSOCIATED ASSOCIATION CROSS LESS MESSAGE PAGE.MESSAGE SP-ASSIGN SP.ASSIGN SSELECT SUPPRESS UNASSIGN.

The following example matches two alphabetic characters, followed by the letter T:

LIST VOC WITH @ID LIKE "2A'T'"

It lists items such as: CRT FMT NOT PCT SET.

The following example matches two alphabetic characters, followed by the letter T, followed by two alphabetic characters:

LIST VOC WITH @ID LIKE "2A'T'2A"

It lists items such as: AFTER OUTER TOTAL.

The following example matches any value consisting of two alphabetic characters:

LIST VOC WITH @ID LIKE "2A"

It lists items such as: AN AS AT BY CS CT ED EQ GE GT IF IN IS LE LT MD ME NE NO OF ON OR SH TO ZH.

The following example matches two alphabetic characters, followed by a hyphen, followed by any number of characters of any type:

LIST VOC WITH @ID LIKE "2A'-'..."

It lists items such as: BY-DSND BY-EXP BY-EXP-DSND ID-SUP ID-SUPP NI-SUPP SP-ASSIGN SP-COPIES SP-CREATE SP-DELETE SP-DEVICE SP-EDIT SP-FORM SP-GLOBAL SP-KILL SP-OPTS SP-RESUME SP-START SP-STOP SP-SUSPEND.

Equality Tests without WITH

You can perform an equality condition test without the WITH keyword (or any of its synonyms). The results are the same, but the processing is different, as shown in the following examples:

LIST VOC F1 WITH F1="PH"

returns the following:

VOC......... F1.............
 
COL-HDR-SUPP PH
COL.HDR.SUPP PH
 
2 Items listed.

LIST VOC F1="PH"

returns the following:

VOC......... F1.............
 
COL-HDR-SUPP PH
COL.HDR.SUPP PH
 
478 Items listed.

Note the difference in the count of items listed. This behavior is supported for Caché MultiValue and all emulations except INFORMATION, PIOpen, Prime, UniData, and UniVerse.

Condition Tests on @ID without WITH

When performing an equality or LIKE condition test on the @ID dictionary entry, you can omit the WITH keyword. The @ID entry is assumed. The following pairs of commands are equivalent:

LIST VOC WITH @ID > "W"
LIST VOC > "W"

LIST VOC WITH @ID LIKE ...A
LIST VOC LIKE ...A

LIST VOC WITH @ID LIKE "2A'-'..." AND @ID > "SP"
LIST VOC LIKE "2A'-'..." AND > "SP"

Note that in these cases, there is no difference in the count of items listed. This behavior is supported in all emulations.

You can use the FOR keyword to clarify such condition tests. FOR provides no additional functionality, but can be useful in clarifying code, as shown in the following two examples:

LIST VOC F1 F4 LIKE ...A

In this example, the condition test is performed on the @ID dictionary entry, not on F4. Inserting a FOR keyword can help to clarify this logic, as shown in the following functionally identical example:

LIST VOC F1 F4 FOR LIKE ...A

Information on specifying an @ID value using double quotes or single quotes in different emulations, refer to the Emulation section below.

Explicit Logical Operators

The WITH clause supports the AND and OR explicit logical operators.

• The AND keyword is a synonym for the & symbol. The EVERY logical operator is also a synonym for AND, as described below.

• The OR keyword specifies a logical inclusive OR. To specify a logical exclusive OR (an XOR) you must use the WHEN clause with the ASSOCIATED keyword.

When using explicit logical operators, multiple WITH keywords are optional. WITH can be specified for each condition test, or specified only for the first condition test. The following statements are equivalent:

LIST VOC F4 WITH F4>"F" AND WITH F4 LIKE ...S
LIST VOC F4 WITH F4>"F" AND F4 LIKE ...S 

The following examples shows multiple condition tests with explicit logic:

LIST VOC F1 WITH WITH F1="PH" OR F1="F"
LIST VOC F4 WITH F4>"F" AND F4 LIKE ...S 

The EVERY logical operator is a synonym for AND, with the following difference: EVERY can be specified before the first condition test, as well as between condition tests. The following statements are equivalent:

LIST VOC F4 WITH F4>"F" AND WITH F4 LIKE ...S
LIST VOC F4 WITH F4>"F" EVERY F4 LIKE ...S
LIST VOC F4 WITH EVERY F4>"F" EVERY F4 LIKE ...S  

EACH is a synonym for EVERY.

Equality Tests and Implicit OR Logic

When performing multiple equality tests using explicit logic, you can omit repeating the equality operator for each test. For example, the following statements are equivalent:

LIST VOC F1 WITH F1="F" OR F1="PH"
LIST VOC F1 WITH F1="F" OR "PH"

When performing multiple equality tests with OR logic, you can also omit the OR keyword. For example, the following statements are equivalent:

LIST VOC F1 WITH F1="PA" OR "PH" OR "S"
LIST VOC F1 WITH F1="PA" "PH" "S"

Implicit OR logic is only supported for true equality tests (=, <=, >=), not for #, <, or > comparisons. This use of implicit OR logic for equality tests is supported in all emulations.

Conditional Tests and Implicit AND Logic

For all multiple conditional tests, except equality tests, Caché MultiValue uses implicit AND logic. For example, the following statements are equivalent:

LIST VOC F1 WITH F1 > "J" AND F1 < "M"
LIST VOC F1 WITH F1 > "J" < "M"

The following example uses implicit AND logic for condition tests on the F1 and F2 fields:

LIST VOC F1 WITH F1="K" F2 LIKE "...L"

This kind of implicit logic is emulation-dependent. Caché MultiValue and some emulations support implicit AND logic, other emulations apply implicit OR logic. These emulations are further described below.

ONLY Connective Keyword

The ONLY keyword specifies that the condition that follows it applies to the @ID field. The ID.ONLY keyword is a synonym for ONLY.

The following example uses explicit AND logic to test two different fields. It tests for the existence of an F4 value, and for a record ID having a value ending with the letter “L”:

LIST VOC F4 WITH F4 AND @ID LIKE ...L

The same results can be returned using the ONLY connective keyword, which both provides implicit AND logic and an implicit field of @ID:

LIST VOC F4 WITH F4 ONLY LIKE ...L

Without the ONLY connective keyword, the above example would perform a single condition test, returning F4 values that end with the letter “L”.

The ONLY keyword suppresses line wrapping of @ID values when the @ID is the only field displayed. Compare LIST VOC WITH F2 AND @ID LIKE A... which wraps long @ID values, and LIST VOC WITH F2 ONLY LIKE A... which does not wrap long @ID values.

The ONLY keyword always overrides the ID-SUPP keyword.

Order of Logical Condition Testing

In Caché MultiValue, logical conditions are tested in left-to-right order; OR and AND have equal precedence. Logical conditions can be grouped using parentheses to establish a different order of evaluation.

The following example uses left-to-right order. First all items with F1="PH" are selected, then all items with F1="F" are selected, and then the @ID value of these selected items is tested using a pattern match test:

LIST VOC F1 WITH F1="PH" OR F1="F" AND @ID LIKE "3A'-'0X"

This results in the following:

VOC......... F1.............
 
COL-HDR-SUPP PH
TCL-STACK    F

In the following example, all items with F1="PH" are selected, then all items with F1="F" and an @ID value tested using a pattern match test are selected:

LIST VOC F1 WITH F1="PH" OR (F1="F" AND @ID LIKE "3A'-'0X")

This results in the following:

VOC......... F1.............
 
COL-HDR-SUPP PH
COL.HDR.SUPP PH
TCL-STACK    F

Omitting the WITH Keyword

In INFORMATION, PIOpen, Prime, UDPICK, UniData, and UniVerse emulations, the WITH or WHEN keyword is mandatory for a conditional expression clause on a specified field. When the keyword is omitted, the conditional expression always tests @ID, as shown in the following:

LIST VOC F1 = "A"

returns the @ID and F1 of the one item with @ID = “A”

LIST VOC F1 # "A"

returns the @ID and F1 for all items, except the one item with @ID = “A”.

In Caché and all other emulations the conditional expression tests the preceding field name, in this case F1.

Logical Precedence

In Caché MultiValue, logical conditions are tested in left-to-right order; OR and AND have equal precedence. In some emulations, including PICK and Reality, AND has higher precedence than OR.

Implicit Logic

Caché MultiValue uses implicit AND logic when two (or more) conditions are specified without explicit AND or OR keywords. Other emulations uses implicit OR logic in the same circumstances.

Double Quotes and Single Quotes

Caché MultiValue and most emulations make a distinction between single quotes and double quotes in implied equality tests.

The following single quote syntax is parsed in Caché MultiValue and all emulations to return items that have an F1 value (F1 is not null) and that have a @ID value of ASSOC:

LIST VOC F1 WITH F1 'ASSOC'

Thus, all emulations return one item: ASSOC.

The following double quote syntax is parsed differently in Caché MultiValue and some emulations.

LIST VOC F1 WITH F1 "PH"

In Caché MultiValue and most emulations, this is parsed to return all items with F1="PH".

In INFORMATION, PIOpen, Prime, UniData, and UniVerse emulations, this is parsed to return all items that have an F1 value (F1 is not null) and that have a @ID value of “PH”. Thus, these emulations treat double quotes the same as single quotes in this type of syntax.

Pattern Match Wildcards

Some MultiValue emulations support both the LIKE clause pattern match operators and a set of pattern match wildcards which are not used with the LIKE keyword. The following wildcards are supported by MultiValue emulations:

These wildcards are supported by D3, IN2, jBASE, MVBase, PICK, R83, POWER95, Reality, and Ultimate emulations. Caché MultiValue does not support these ^, [, or ] pattern match wildcards.

In the emulations that support these wildcards, the following statement is parsed to match F5 values to the pattern of the literal character 2 followed by any single character:

LIST VOC F5 WITH F5="2^"

This is functionally identical to:

LIST VOC F5 WITH F5 LIKE "'2'1X"

In the emulations that support these wildcards, the following example is parsed to match @ID values to the pattern of the any number of characters, followed by the literal characters SU, followed by any single character, followed by the literal character P, followed by any number of characters:

LIST VOC WITH @ID = [SU^P]

This is functionally identical to:

LIST VOC WITH @ID LIKE "0X'SU'1X'P'0X"

Both examples return: COL-HDR-SUPP COL-SUPP COL.HDR.SUPP DET-SUPP HDR-SUPP ID-SUPP NI-SUPP SP-SUSPEND SP.SUSPEND SUPP SUPPRESS.