SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-31
Using Subqueries
ORDERNUM,
QTY_AVAILABLE,
QTY_ORDERED
FROM PARTS, ODETAIL X
WHERE PARTS.PARTNUM = X.PARTNUM
AND (?PERCENT/100) * QTY_AVAILABLE
< ( SELECT SUM(QTY_ORDERED)
FROM ODETAIL
WHERE X.PARTNUM = ODETAIL.PARTNUM)
ORDER BY X.PARTNUM;
DETAIL PARTNUM AS I4 HEADING 'PART',
PARTDESC,
QTY_AVAILABLE AS I6 HEADING 'AVAILABLE',
ORDERNUM AS I6 HEADING 'ORDER NO.',
QTY_ORDERED AS I5 HEADING 'ORDERED';
BREAK ON PARTNUM, PARTDESC, QTY_AVAILABLE;
SUBTOTAL QTY_ORDERED OVER QTY_AVAILABLE;
To execute this query using a value of 5 percent, enter:
>> SET PARAM ?PERCENT 5;
>> OBEY ORDERPCT;
>> LIST ALL;
The report in Figure 3-8 shows that part numbers 2001 and 6400 satisfy the conditions
of the query.
If you do not specify OVER QTY_AVAILABLE in the SUBTOTAL command, the
subtotals are calculated when any break column value changes. In this example, the
values in all three break columns change at the same time. Therefore, printing the
subtotals for each break column is redundant.