NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
Q-6
Considerations—Quantified Predicate
Considerations—Quantified Predicate
•
QUANTIFIED is a comparison predicate. See Comparison Predicate on page C-53
for a discussion of general rules for comparisons and specific information about
comparing character data (including character data associated with collations),
numeric data, date-time data, and interval data.
•
The subquery result must be a table of one column. The data type of the first
expression must be compatible with the data type of the subquery result column.
•
If you specify ALL, the predicate is true if either of the following is true:
°
The comparison is true for every value selected by subquery.
°
The subquery selects no values.
•
If you specify ANY, the predicate is true if the comparison is true for at least one
value selected by subquery. The predicate is false if subquery selects no value, or
if the comparison is false for every value selected. SOME is a synonym for ANY.
Specifying =ANY is the same as specifying the IN predicate, although <>ANY is
not the same as NOT IN.
Examples—Quantified Predicate
•
The following predicate finds all salaries that are greater than the salaries of all the
employees who have a jobcode of 420:
SALARY > ALL (SELECT SALARY
FROM EMPLOYEE WHERE JOBCODE = 420)
•
The following predicate finds all part numbers that are equal to any part number
with more than five units in stock:
PARTNUM = ANY (SELECT PARTNUM
FROM ODETAIL WHERE QTY_ORDERED > 5)