SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-30
Using Subqueries
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 to
perform this operation. You could use the implicit correlation name in the outer query.
This specification uses an implicit correlation name to achieve the same result as the
preceding query:
>> SELECT SUPPNUM, PARTNUM, PARTCOST
+> FROM PARTSUPP
+> WHERE PARTCOST > (SELECT AVG(PARTCOST)
+> FROM 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 SAMDBCAT.INVENT.PARTSUPP PS
+> WHERE PARTCOST > ANY (SELECT PRICE
+> FROM SAMDBCAT.SALES.PARTS
+> WHERE SAMDBCAT.SALES.PARTS.PARTNUM = PS.PARTNUM);
S> LIST NEXT 5;
PARTNUM SUPPNUM PARTCOST QTY_RECEIVED
------- ------- ------------ ------------
212 6 3000.00 2
***WARNING from MXCI[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. You can enter the percent
as a parameter each time you execute the query, which is in a command file named
ORDERPCT.
SET LIST_COUNT 0;
SELECT X.PARTNUM,
PARTDESC,