Quantified Predicate

Use the quantified predicate to compare a value with a collection of values.

A quantified predicate has the same form as a basic predicate except the second operand is a subselect preceded by ANY, ALL, or SOME. The subselect may return any number of values.

Following is the syntax diagram for a quantified predicate:

Note: The following are CA Datacom/DB extensions:

¬= is a CA Datacom/DB extension.
¬< is a CA Datacom/DB extension.
¬> is a CA Datacom/DB extension.

►►─ expression ─┬─ = ─────┬─┬─ ALL ──┬─ (subselect) ──────────────────────────►◄
                ├─ < ─────┤ ├─ ANY ──┤
                ├─ > ─────┤ └─ SOME ─┘
                ├─ <> ────┤
                ├─ <= ────┤
                ├─ >= ────┤
                ├─ ¬= ────┤
                ├─ ¬< ────┤
                └─ ¬> ────┘

Description

expression

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

(subselect)

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

ALL

The result of the predicate is true if the subselect returns no values or if the specified relationship is true for every value returned by the subselect.

The result is false if the specified relationship is false for at least one value returned by the subselect.

ANY

The result of the predicate is true if the specified relationship is true for at least one value returned by the subselect.

The result is false if the subselect returns no values or if the specified relationship is false for every value returned by the subselect.

SOME

The result of the predicate is true if the specified relationship is true for at least one value returned by the subselect.

The result is false if the subselect returns no values or if the specified relationship is false for every value returned by the subselect.

Example

The following example of a quantified predicate specifies that value of PAY must be greater than or equal to each SALARY value returned by the SELECT. The result is true if that is the case. However, if the value of PAY is less than one of the values returned by the SELECT, then the result is false.

 PAY >= ALL(SELECT SALARY
            FROM EMP)