IN Predicate

You use the IN predicate to compare a value with a collection of values. Following is the syntax diagram of the IN predicate:

Note: The special-register is a CA Datacom/DB extension. See Special Registers.

►►─ expression ─┬───────┬─ IN ─┬─ (subselect) ──────────────────────┬─────────►◄
                └─ NOT ─┘      │     ┌─ , ────────────────────┐     │
                               └─ ( ─▼─┬─ host-variable ────┬─┴─ ) ─┘
                                       ├─ literal ──────────┤
                                       └─ special-register ─┘

Description

expression

Specify an expression. For more information about expressions, see Expressions.

NOT

A keyword. Use NOT to specify that only values that do not match the comparison be selected.

IN

Introduces the collection of values used in the comparison.

(subselect)

Specify a subselect. The subselect must be enclosed by parentheses. For more information about the subselect see Subselect.

host-variable

Specify a host-variable. Each host-variable specified must identify a variable that is described in the program under the rules for declaring host-variables. See Host Variables for more information.

Use a comma to separate the host-variables and enclose the list in parentheses.

special-register

Specify a special-register. See Special Registers for more information on special-registers.

literal

Specify a literal. If the expression is numeric, the literal must be numeric.

Use a comma to separate the literals and enclose the list in parentheses.

The following table shows forms of the IN predicate and the predicate form to which it is equivalent.

The IN predicate form:	expression IN expression
is equivalent to:	a basic predicate
of the form:	expression = expression
The IN predicate form:	expression IN (subselect)
is equivalent to:	a quantified predicate
of the form:	expression = ANY (subselect)
The IN predicate form:	where the second operand is a collection of one or more values specified by any combination of literals, host-variables or the keyword USER
is equivalent to:	a quantified predicate
of the form:	expression = ANY (subselect)
except:	the second operand consists of the specified values rather than the values returned by a subselect

Example

The following IN predicate is true only if the value for DEPT is equal to any of the specified literals, A2, B1 or C3. The result is false if the value for DEPT is not equal to any of the literals.

 DEPT IN ('A2', 'B1', 'C3')