SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-10
Comparing a Set of Columns to a Set of Values
In the next example, the subquery (shown in boldface type) selects from the PARTLOC
table part numbers for parts with greater than 500 units on hand in a single location.
The main query selects the part description and price for these parts from the PARTS
table.
>> SELECT PARTNUM, PARTDESC, PRICE
+> FROM SAMDBCAT.SALES.PARTS
+> WHERE PARTNUM IN (SELECT PARTNUM
+> FROM SAMDBCAT.INVENT.PARTLOC
+> WHERE QTY_ON_HAND > 500);
S> LIST NEXT 2;
PARTNUM PARTDESC PRICE
------- ------------------ ------------
2001 GRAPHIC PRINTER,M1 1100.00
2403 DAISY PRINTER,T2 650.00
S>
Note that a subquery select list can contain only one element. The element can be an
expression or column name.
To confirm the result of the previous query, you can select the PARTLOC information
for the two parts. The QTY_ON_HAND value is greater than 500 in at least one
location.
>> SELECT * FROM INVENT.PARTLOC
+> WHERE PARTNUM IN ( 2001, 2403 );
S> LIST ALL;
LOC_CODE PARTNUM QTY_ON_HAND
-------- ------- -----------
A10 2001 800
A88 2403 735
G88 2403 32
P10 2001 0
--- 4 row(s) selected.
Comparing a Set of Columns to a Set of Values
You can use comparison and BETWEEN predicates to select rows based on values
from more than one column in the row when data types of the expressions are
compatible.
For example, you might want to select a range of rows from a list of names by
examining both the first and last names. In the EMPLOYEE table, these names are
stored in separate columns.