SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-29
Using Subqueries
+> WHERE PARTNUM = 2003);
S> LIST ALL;
SUPPNUM PARTNUM PARTCOST
------- ------- --------
2 2003 1400.00
10 2003 1450.00
--- 2 row(s) selected.
>>
This query finds all suppliers who charge more than the minimum price for part number
2003. The subquery is evaluated once to determine the minimum cost for the part.
Each row selected by the main query is compared to the result of the subquery.
You can replace the numeric literal 2003 with the parameter ?PART to create a
general query for gathering this information for any part.
The following commands illustrate a correlated query which finds suppliers whose price
exceeds the average price for a part:
>> SELECT SUPPNUM, PARTNUM, PARTCOST
+> FROM INVENT.PARTSUPP XP
+> WHERE PARTCOST > (SELECT AVG(PARTCOST)
+> FROM INVENT.PARTSUPP P
+> WHERE XP.PARTNUM = P.PARTNUM)
+> ORDER BY SUPPNUM;
S> LIST ALL;
SUPPNUM PARTNUM PARTCOST
------- ------- --------
1 212 2000.00
1 244 2400.00
1 255 3300.00
1 2405 500.00
2 2001 750.00
2 2003 1400.00
. . .
. . .
15 4102 21.00
--- 18 row(s) selected.
>>
The subquery is evaluated for each row selected by the main query. The FROM clause
of the main query defines the correlation name XP for the PARTSUPP table. The
subquery defines the correlation name P for the PARTSUPP table. The WHERE
clause defines the correlation. The subquery averages rows from the PARTSUPP table
with a PARTNUM value equal to the PARTNUM value of the current row from the outer
query.
You do not have to define a correlation name for both the main query and subquery in
order to perform this operation. You could use the implicit correlation name in the outer
query.