SQL/MP Report Writer Guide

Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-57
Calculating Subtotals
command. You can use the UNION operator to specify a select list column that
contains a conditional value and can be subtotaled.
In the following example, a union of three SELECT statements (shown in boldface
type) 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.
>> VOLUME SALES;
>> SET LIST_COUNT 0;
>> 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-23 shows the first few lines of the resulting report.