SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-9
Developing a Query
The next quantified predicate selects rows with a part cost equal to any of the parts
supplied by supplier number 1:
+> AND PARTCOST = ANY ( SELECT PARTCOST FROM PARTSUPP
+> WHERE SUPPNUM = 1 ) ;
For more examples of quantified predicates as well as examples of EXISTS
predicates, see the SQL/MP Reference Manual.
•
The following BETWEEN predicate specifies only suppliers of parts numbered from
4000 through 6103:
+> AND P.PARTNUM BETWEEN 4000 AND 6103;
•
The IN predicate can locate a specific list of values, or all values except those in a
specific list. This predicate allows you to express multiple conditions efficiently.
+> AND P.PARTNUM IN (1, 3, 15);
+> AND P.PARTNUM NOT IN (4, 7, 21, 45);
You can also use NOT with the LIKE, EXISTS, and NULL predicates.
You can use a subquery to select a list of values. This technique allows you to
compare information from another table without joining tables in the main query. The
subquery selects the rows you want to compare.
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 SALES.PARTS
+> WHERE PARTNUM IN (SELECT PARTNUM
+> FROM 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.