SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-9
Setting Criteria for Selecting Data
These examples illustrate the effect of each predicate when included in the SELECT
command:
>> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SAMDBCAT.SALES.PARTS P,
+> SAMDBCAT.INVENT.PARTSUPP PS,
+> SAMDBCAT.INVENT.SUPPLIER S
+> WHERE P.PARTNUM = PS.PARTNUM
+> AND PS.SUPPNUM = S.SUPPNUM
+> ( Substitute predicate example from following text.)
•
This comparison predicate specifies only suppliers identified by numbers less than
or equal to 400:
+> AND S.SUPPNUM <= 400;
•
Use quantified predicates to select rows based on their relation to all or any rows
selected by a separate search condition. For example, you can select PARTSUPP
table rows that contain a part cost value greater than the cost of every part in the
table supplied by supplier number 6:
+> AND PARTCOST > ALL ( SELECT PARTCOST FROM PARTSUPP
+> WHERE SUPPNUM = 6 ) ;
The subquery selects the part cost from each row describing parts supplied by supplier
number 6. The greatest part cost is $1100.00. The main query selects rows with a part
cost value greater than all values selected by the subquery, or rows with a part cost
greater than $1100.00.
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/MX Reference Manual.
•
This BETWEEN predicate specifies only suppliers of parts numbered from 4000
through 6103:
+> AND P.PARTNUM BETWEEN 4000 AND 6103;
•
Use the IN predicate to locate a specific list of values or all values except those in
a specific list. Use this predicate 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, which enables you to compare
information from another table without joining tables in the main query. The subquery
selects the rows you want to compare.