Skip to main content

WITH clause

Specifies a condition for query results.

Synopsis

WITH condition

Arguments

condition A boolean condition expression, or a series of condition expressions associated with AND or OR logical operators.

Description

The WITH clause limits the items returned by the query to the ones whose value passes the condition test.

A WITH clause condition test consists of a boolean comparison statement, such as field = value, where field is a dictionary entry field, and value is a literal value. A variety of comparison operators can be used.

Multiple condition tests can be associated using the AND or OR logical operators: field1 = value1 AND field2 = value2. Further details are provided in the “Multiple Condition Tests” section below.

When multiple CMQL clauses are specified the clauses may be specified in any order. The order of application of CMQL clauses is as follows: the WITH clause is always applied first. Its results are supplied to the SAMPLED clause (if present), and the results are supplied to the SAMPLE clause (if present).

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

Value Test

Specifying WITH field limits the items returned by the query to the records that have a value for the specified dictionary entry field. This excludes null fields.

In the following example, the WITH clause limits the result set to those records that have value for field F4:

LIST VOC F4 WITH F4

It lists the following 15 items: BSELECT COUNT.VERB LIST LIST.ITEM LIST.LABEL PRINT.CATALOG REFORMAT SELECT SORT SORT.ITEM SORT.LABEL SREFORMAT SSELECT STAT SUM.VERB. All of these items have an F4 field value. Note that PRINT.CATALOG is listed, but [ is not, though both have an F4 field that apparently display no value. The difference is that PRINT.CATALOG has an F4 field with a value of " " (one blank space) and thus has a value; [ has an F4 field that is null and thus has no value.

Null Test

There are several ways to test whether an item is null (has no assigned value). You can use a value test, an equality operator with null symbolized using "", or a keyword.

The following statements return non-null values and exclude nulls:

  • WITH field

  • WITH field IS.NOT.NULL

  • WITH field # ""

The following statements return nulls and exclude non-null values:

  • WITH NO field

  • WITH field IS.NULL

  • WITH field = ""

All values are greater than "", no values are less than "". The MultiValue equality operators can return null values. The BETWEEN clause cannot return null values. Therefore, in the following example the first two statements are functionally identical, but the second two statements are not:

LIST VOC WITH F4 >= "DA" AND <= "J"
LIST VOC WITH F4 BETWEEN "DA" "J"

LIST VOC WITH F4 >= "" AND <= "J"
LIST VOC WITH F4 BETWEEN "" "J"

Equality Operators

You can perform an equality comparison using any of the following operators:

=

EQ

EQUAL

Equal to.

#

<>

NE

NOT

Not equal to.

<

LT

LESS

BEFORE

Less than.

<=

LE

Less than or equal to.

>

GT

GREATER

AFTER

Greater than.

>=

GE

Greater than or equal to.

The following example limits the query result set to those records with a field F4 value of “L”:

LIST VOC WITH F4="L"

It lists the following 3 items: BSELECT COUNT.VERB SELECT.

The following three statements are functionally identical:

LIST VOC WITH @ID EQ "ASSOC"
LIST VOC WITH @ID = "ASSOC"
LIST VOC WITH @ID="ASSOC"

Spaces before and after symbolic operators are not required, but spaces are required for alphabetic code operators.

Values should be enclosed with quote characters to avoid ambiguity, but quotes are not required in all cases, as shown in the following:

LIST VOC WITH F1 = "PH"
LIST VOC WITH F1 = PH

In Caché MultiValue, the equal sign is optional; a condition expression defaults to an equality test, as shown in the following example:

LIST VOC WITH F1 "PH"

This equality test using double quotes is supported for Caché MultiValue and all emulations except INFORMATION, PIOpen, Prime, UniData, and UniVerse. For further details on these emulations and the use of single quotes, refer to the Emulation section below.

The less than and greater than operators select according to ascending collation sequence. The specified limiting value does not need to exist in the file.

The following example searches the @ID dictionary entry field of the VOC for all values less than AM. The LIST command lists those items that pass this condition test:

LIST VOC WITH @ID LT "AM"

It lists items such as: # &COMO& &PH& &SAVEDLISTS& ; @CMQLOPTS A ABORT AFTER ALL ALL.MATCH.

The following example searches the @ID dictionary entry field of the VOC for all values greater than W. The LIST command lists those items that pass this condition test:

LIST VOC WITH @ID > "W"

It lists items such as: WHEN WHERE WHERE.VERB WHO WITH WITHIN WITHOUT Z ZH [.

LIKE Pattern Match Operator

The LIKE operator matches the value of a field to a pattern and returns a boolean value. A pattern may consist of either a pattern match code string, or some combination of pattern match codes and literal substrings. Pattern match codes are used to specify an expected pattern of character types to match with the field value, and/or a location in the field value to search for a literal substring. The substring may be a single character or several characters. Note that string comparisons are case-sensitive.

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:

Pattern Match Code Meaning

SS...

“’SS’...”

Trailing ellipsis — A literal substring (here the string SS) followed by any number of characters of any type. The ellipsis must appear at the end of a pattern match string.

...SS

“...’SS’”

Leading ellipsis — Any number of characters of any type followed by a literal substring (here the string SS). The ellipsis must appear at the beginning of a pattern match string.
“nA” A — A specified integer number (n) of alphabetic characters. You can use “0A” to specify any number of alphabetic characters. The nA pattern code may appear anywhere in a pattern match string.
“nN” N — A specified integer number (n) of numeric characters. You can use “0N” to specify any number of numeric characters. The nN pattern code may appear anywhere in a pattern match string.
“nX” X — A specified integer number (n) of characters of any type. You can use “0X” to specify any number of characters of any type. The nX pattern code may appear anywhere in a pattern match string.

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.

Note:

Caché MultiValue does not support the use of the “}” (right curly brace) character to delimit multiple match patterns. Code from other MultiValue implementations (such as Pick and UniVerse) that uses this syntax must be changed to replace the right curly brace with the OR logical operator.

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.

SAID and SPOKEN Pattern Match Operators

The SAID pattern match operator returns data values that sound like the specified value. The first character of the SAID value must match exactly with the first letter of the data value. The other characters follow Soundex conventions. SAID pattern matching is not case-sensitive.

The following example returns “BASIC”:

LIST VOC WITH @ID SAID BSIK

These two values match because both are Soundex B220. Note that neither BSC nor BASI return “BASIC” because both of these are Soundex B200.

SAID ignores all non-alphabetic characters. For example:

LIST VOC F5 WITH F5 SAID CPN

Returns the F5 values CPUN, 2CPUN, and 2CPM.

You can specify SAID * to return all values that contain no alphabetic characters.

SPOKEN is a synonym for SAID.

For further details on Soundex analysis, refer to the MVBasic SOUNDEX function in the Caché MultiValue Basic Reference.

Alternative Syntax Omitting the WITH Keyword

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.

Multiple Condition Tests

A WITH clause can contain more than one condition test. These condition tests can be applied to the same field or to different fields. They can be associated with an explicit logical operator or with an implicit logical operator.

By default, multiple condition tests are applied in left-to-right order.

In the following example the F4 field is being tested for both having a value greater than “F” and having a value ending with the letter “S”:

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

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

Emulation

This section describes differences in the parsing of conditional expressions in different MultiValue emulations.

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.

Implicit AND Implicit OR

Cache

INFORMATION

PIOpen

Prime

UniData

UniVerse

D3

IN2

jBASE

MVBase

PICK

R83

POWER95

Reality

Ultimate

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:

^ A single character wildcard.
[ A multiple character wildcard. Can be zero characters, one character, or multiple characters.
] A multiple character wildcard. Can be zero characters, one character, or multiple characters.

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.

FeedbackOpens in a new tab