SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-87
Examples—SUBTOTAL
enough room for the subtotal. If the subtotal is too large for its display format, the
field is filled instead with overflow characters.)
Subtotals print on three lines: the first line contains underline characters (see
UNDERLINE_CHAR Option on page U-1), the second line contains the subtotal
value, and the third line is blank.
To identify the break group for the subtotal, the report writer prints a subtotal label
(see SUBTOTAL_LABEL Option on page S-88) under the break column. If the
label does not fit in the break column, the label is truncated. An asterisk is the
default subtotal label.
If the subtotal column is the same as the break column, both the subtotal label and
the subtotal value must print under that column. If the column is wide enough to
accommodate both the label and the value, both are printed; otherwise, the label is
truncated and can be entirely overwritten by the subtotal value.

In calculating subtotals, the report writer uses the maximum format for the item's
data type and the same scale as the item to be subtotaled. In unusual cases (such
as when an expression contains an item multiplied by an extremely small fractional
value) this strategy can cause numeric overflow.
Specifying small numeric values in exponential notation (for example,
.0000246615 E0 instead of .0000246615) can prevent overflow by causing the
report writer to use a floating-point format for such calculations.
Examples—SUBTOTAL

This example selects data ordered by the value in column ORDERNUM, then
generates a subtotal for the expression used as column 3 whenever the value of
ORDERNUM changes:
>> SET LIST_COUNT 0;
>> SELECT ORDERNUM, PARTNUM, (QTY_ORDERED * UNIT_PRICE)
+> FROM SALES.ODETAIL ORDER BY ORDERNUM;
S> BREAK ON ORDERNUM;
S> SUBTOTAL COL 3 OVER ORDERNUM;

This example selects data ordered by the values in columns DEPTNUM and
JOBCODE, and then generates subtotals for salaries and bonuses for each job
code. (The example uses an asterisk as the default subtotal label. To learn how to
change the default subtotal label, see SUBTOTAL_LABEL Option on page S-88.)
>> SET LIST_COUNT 0;
>> SELECT DEPTNUM, JOBCODE, SALARY, SALARY*.025
+> FROM PERSNL.EMPLOYEE ORDER BY DEPTNUM, JOBCODE;
S> DETAIL DEPTNUM, JOBCODE, SALARY AS F15.2, COL 4 AS F12.2
+> HEADING "BONUS";
S> BREAK ON DEPTNUM, JOBCODE;
S> SUBTOTAL SALARY, COL 4;
S> LIST ALL;