SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-30
Using Subqueries
The following specification uses an implicit correlation name to achieve the same result
as the preceding query:
>> SELECT SUPPNUM, PARTNUM, PARTCOST
+> FROM INVENT.PARTSUPP
+> WHERE PARTCOST > (SELECT AVG(PARTCOST)
+> FROM INVENT.PARTSUPP P
+> WHERE PARTSUPP.PARTNUM = P.PARTNUM)
+> ORDER BY SUPPNUM;
Defining explicit correlation names provides clearer documentation of what the query
does.
You can use quantified predicates for selecting rows in relation to all or any of the rows
selected by a different search condition. For example, you can select PARTSUPP table
rows that contain a part cost greater than the suggested price for the part in the
PARTS table:
>> SELECT *
+> FROM PARTSUPP PS
+> WHERE PARTCOST > ANY (SELECT PRICE
+> FROM SALES.PARTS
+> WHERE PARTS.PARTNUM = PS.PARTNUM);
S> LIST NEXT 5;
PARTNUM SUPPNUM PARTCOST QTY_RECEIVED
------- ------- ------------ ------------
212 6 3000.00 2
***WARNING from SQLCI[10098] There are no more selected rows.
S>
The quantified predicate is evaluated for each row selected by the outer query. The
subquery selects the PARTCOST from the PARTS table for the part with the same
number as the current row of the outer query. Only one part qualifies for selection.
In the next example, the subquery sums the quantity ordered of each part retrieved in
the outer query. The main query selects parts for which the quantity on order is greater
than or equal to a specified percent of the parts available. The query allows you to
enter the percent as a parameter each time you execute the query. The query is in an
command file named ORDERPCT.