ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 119
SQL Queries
Complex Queries
Note that you could get the same information in other ways. For example, you could use
two separate queries. Alternatively, you could use two predicates in the search condition
joined by the OR operator as follows:
SELECT PartNumber
FROM PurchDB.Supplyprice
WHERE DeliveryDays >= 30 OR
VendorNumber = 9002
ORDER BY PartNumber
This query still contains duplicate rows where more than one vendor supplies a given part;
but no duplicates are caused by vendor 9002 supplying some parts, and that some of these
take 30 or more days to deliver. The duplicates could be eliminated by using the SELECT
DISTINCT instead of SELECT statement.
Using Character Constants with UNION
If you want to see which SELECT statement in the UNION statement contributed each row to
the query result, you can include character constants in your SELECT statements. A second
column is then generated that shows the originating query block for each row, as in this
example:
SELECT PartNumber, 'deliverydays >= 30'
FROM PurchDB.SupplyPrice
WHERE DeliveryDays >= 30
UNION ALL
SELECT PartNumber, 'supplied by 9002 '
FROM PurchDB.SupplyPrice
WHERE VendorNumber = 9002
ORDER BY PartNumber
----------------+------------------
PARTNUMBER |(CONST)
----------------+------------------
1123-P-01 |deliverydays >= 30
1123-P-01 |deliverydays >= 30 <----
1123-P-01 |supplied by 9002
1133-P-01 |supplied by 9002
1133-P-01 |deliverydays >= 30
1143-P-01 |deliverydays >= 30
1143-P-01 |deliverydays >= 30 <----
1153-P-01 |deliverydays >= 30
1153-P-01 |supplied by 9002
1223-MU-01 |deliverydays >= 30
1233-MU-01 |deliverydays >= 30
1323-D-01 |deliverydays >= 30
1333-D-01 |deliverydays >= 30
1343-D-01 |deliverydays >= 30
1523-K-01 |deliverydays >= 30
1623-TD-01 |deliverydays >= 30
1823-PT-01 |supplied by 9002
1923-PA-01 |supplied by 9002