ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 121
SQL Queries
Complex Queries
The comparison operators shown here are allowable:
= <> < > <= >=
The quantifier is one of these three keywords:
ALL ANY SOME
The value list is of this form:
(
Val1 , Val2 , ..., Valn )
Using the ANY or SOME Quantifier with a Value List
With the ANY or SOME quantifier (ANY and SOME are synonymous), the predicate is true if
any of the values in the value list or subquery relate to the expression as indicated by the
comparison operator.
Suppose you have a list of the part numbers for parts you have been buying from vendor
9011. You would like to start obtaining those parts from other vendors. The following
example shows how you would find the part number and vendor number for all parts
supplied by vendor 9011 that are also supplied by some other vendor:
SELECT PartNumber, VendorNumber
FROM PurchDB.SupplyPrice
WHERE PartNumber = ANY
('1343-D-01', '1623-TD-01', '1723-AD-01', '1733-AD-01')
AND NOT VendorNumber = 9011
----------------+------------
PARTNUMBER |VENDORNUMBER
----------------+------------
1343-D-01 | 9001
1623-TD-01 | 9015
1723-AD-01 | 9004
1723-AD-01 | 9012
1723-AD-01 | 9015
1733-AD-01 | 9004
1733-AD-01 | 9012
The quantifier ANY is used to determine whether PurchDB.SupplyPrice contains any of the
part numbers in the value list. If so, the query returns the part number and vendor
number of vendors supplying that part. The final predicate eliminates all instances where
the part is supplied by vendor 9011. Note that SOME could be used in place of ANY, because
SOME and ANY are synonyms.