SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-29
Using Subqueries
A correlated subquery is evaluated for each row selected by the main query. A
subquery that does not contain a correlated reference is evaluated once. The result is
used for evaluating the WHERE clause against each row selected by the main query.
These following commands illustrate a query that does not contain a correlated
reference:
>> SELECT SUPPNUM, PARTNUM, PARTCOST
+> FROM IPARTSUPP
+> WHERE PARTNUM = 2003
+> AND PARTCOST > (SELECT MIN(PARTCOST)
+> FROM PARTSUPP
+> 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.
To create a general query for gathering this information for any part, replace the
numeric literal 2003 with the parameter ?PART.
These following commands illustrate a correlated query which finds suppliers whose
price exceeds the average price for a part:
>> SELECT SUPPNUM, PARTNUM, PARTCOST
+> FROM PARTSUPP XP
+> WHERE PARTCOST > (SELECT AVG(PARTCOST)
+> FROM 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.
>>