NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-85
SUBTOTAL Command
SUBTOTAL Command
SUBTOTAL is an SQLCI report writer command that specifies columns to subtotal and
when to print the subtotals. SUBTOTAL returns you to the first SELECT output row.
column
identifies a column to subtotal. column must be a column with a numeric data type
from the current detail line that is not an IF/THEN/ELSE column.You can specify it
as a column name, an alias, a detail alias, or COL number (which specifies the
position of the column in the select list).
OVER break-col
specifies that the subtotal prints when the data value of the named break column
changes. break-col must be a break column specified in a BREAK ON
command, but you can specify it as a column name, an alias, a detail alias, or COL
number (which specifies the position of the column in the select list).
If you omit the OVER clause, subtotals print when the value in any currently
defined break column changes.
Considerations—SUBTOTAL
•
BREAK ON command required
You must enter a BREAK ON command to define break columns you specify in a
SUBTOTAL command. The BREAK ON command does not have to precede the
SUBTOTAL command, but you must enter it before you list the report.
•
One SUBTOTAL command in effect per break column
When you enter a SUBTOTAL command for a specific break column, it replaces
any existing SUBTOTAL command for that break column. If you enter a
SUBTOTAL command without the OVER clause, it replaces any previous
SUBTOTAL command without an OVER clause.
•
Subtotal formats
A subtotal prints immediately beneath the print item it subtotals, and the subtotal
value prints in the same display format as the print item. (When you specify a print
item in a DETAIL command, check that the display format in the AS clause allows
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.
SUBTOTAL column [, column ] ... [ OVER break-col ] ;