SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-57
Calculating Subtotals on Conditional Values
In this example, a union of three SELECT statements (shown in boldface) retrieves the
data for a report. Column 2 of the detail line contains the number of bonus points the
sales representative has earned based on the quantity of parts ordered by customers:
10 points for up to 5 units of a specific part, 25 points for 6 to 15 units, and 50 points
for more than 15 units. A subtotal of all bonus points for each sales representative is
computed.
>> SELECT SALESREP, 10
+> FROM ORDERS ORD, ODETAIL OD
+> WHERE ORD.ORDERNUM = OD.ORDERNUM
+> AND QTY_ORDERED <= 5
+> UNION ALL
>> SELECT SALESREP, 25
+> FROM ORDERS ORD, ODETAIL OD
+> WHERE ORD.ORDERNUM = OD.ORDERNUM
+> AND QTY_ORDERED BETWEEN 6 AND 15
+> UNION ALL
>> SELECT SALESREP, 50
+> FROM ORDERS ORD, ODETAIL OD
+> WHERE ORD.ORDERNUM = OD.ORDERNUM
+> AND QTY_ORDERED > 15
+> ORDER BY SALESREP;
S> DETAIL SALESREP HEADING ”Sales Representative”,
+> COL 2 HEADING ”Bonus Points” ;
S> BREAK ON SALESREP;
S> SUBTOTAL COL 2 OVER SALESREP ;
S> LIST ALL ;
Figure 4-22 shows the first few lines of the resulting report.