Expansion of Quantified-predicate

The quantified-predicate tests the comparison of a value to either some or all the values in a specified set.
idmscu19
The quantified-predicate tests the comparison of a value to either some or all the values in a specified set.
This article describes the following information:
2
2
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:
    Comparison operator
    Meaning
    =
    Equal to
    <>
    Not equal to
    <
    Less than
    <=
    Less than or equal to
    >
    Greater than
    >=
    Greater than or equal to
    Consideration
    The logical "not" operator can be specified using the code point represented by X'5F' that can vary with the code page being used. For example, the forms ¬=, ¬<, and ¬> are supported in code pages where the logical not sign is X'5F'. The forms ^=, ^<, and ^> are supported in code pages where the circumflex accent is represented by X’5F’. These forms of the operators are intended only to support existing SQL statements that use them and are not recommended for use when writing new SQL statements. The equivalent operator from the table above should be substituted for any operator which includes a not sign. For example, substitute '<>' for '¬=', '<=' for '¬>', and '>=' for '¬<'.
  • ALL
    Directs CA IDMS to test if 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 if 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. 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
Following is the result of a quantified predicate that includes ALL:
  • 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
Following is the result of a quantified predicate that includes SOME or ANY:
  • 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);