NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-86
Examples—SUBTOTAL
To identify the break group for the subtotal, the report writer prints a subtotal label
(see SUBTOTAL_LABEL Option on page S-87) 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.
•
Precision for calculations
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 result in 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
•
The following 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;
•
The following example selects data ordered by the values in columns DEPTNUM
and JOBCODE, then generates subtotals for salaries and bonuses for each job code.
(The example uses an asterisk as the default subtotal label. See
SUBTOTAL_LABEL Option
on page S-87 to learn how to change the default
subtotal label.)
>> 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;