Predicates and Search Condition › Expansion of Quantified-predicate

Expansion of Quantified-predicate

The quantified-predicate tests the comparison of a value to either some or all the values in a specified set.

Syntax

Expansion of quantified-predicate

►►─── value-expression ───────────────────────────────────────────────────────►

 ►─── comparison-operator ─┬─ ALL ──────┬─ ( subquery ) ──────────────────────►◄
                           └─┬─ SOME ─┬─┘
                             └─ ANY ──┘

Parameters

value-expression

Specifies a value to be compared to the set of values. For expanded value-expression syntax, see Expansion of Value-expression.

comparison-operator

Specifies the comparison operator to be used in the test. Valid values for comparison-operator are:

ALL

Directs CA IDMS to test whether the specified value relates to all the values in the test set in the way specified by the comparison operator.

SOME/ANY

Directs CA IDMS to test whether the specified value relates to at least one value in the test set in the way specified by the comparison operator.

SOME and ANY are synonyms.

( subquery )

Specifies a subquery that returns zero or more rows and whose result table consists of a single column. For expanded subquery syntax, see Expansion of Subquery.

Usage

Comparable Data Types

The data types of the values being compared must be comparable.

Note: For more information about comparing values of different data types, see Comparison, Assignment, Arithmetic, and Concatenation Operations.

Truth Table for a Quantified Predicate with ALL

The result of a quantified predicate that includes ALL is:

• True when the value being tested relates to every value in the test set in the way specified by the comparison operator or when the result of the subquery is an empty set
• False when the value being tested does not relate to at least one value in the test set in the way specified by the comparison operator
• Unknown when the value being tested is null or when at least one value in the test set is null and the value being tested relates to all other values in the test set in the way specified by the comparison operator

Truth Table for a Quantified Predicate with SOME or ANY

The result of a quantified predicate that includes SOME or ANY is:

• True when the value being tested relates to at least one value in the test set in the way specified by the comparison operator
• False when the value being tested does not relate to any value in the test set in the way specified by the comparison operator or when the result of the subquery is an empty set
• Unknown when the value being tested is null or when at least one value in the test set is null and the value being tested does not relate to any value in the test set in the way specified by the comparison operator

Examples

Using ALL

The following SELECT statement identifies the employees whose percent of salary increase at their 1999 review was greater than their percent of salary increase in any other year:

select emp_id
   from benefits b1
   where fiscal_year = '99'
      and review_percent > all
         (select review_percent
            from benefits b2
            where b1.emp_id = b2.emp_id
               and fiscal_year <> '99');

Using ANY

The following SELECT statement identifies employees who earned more in commission in the 1999 fiscal year than they did in salary in at least one fiscal year:

select s.emp_id
   from sales s, position p1
   where s.emp_id = p1.emp_id
      and s.fiscal_year = '99'
      and comm_percent * sales_to_date > any
         (select salary_amount
            from position p2
            where s.emp_id = p2.emp_id);