NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
T-9
TOTAL Command
TOTAL Command
TOTAL is an SQLCI report writer command that specifies columns in a report for which
to calculate and print totals. TOTAL returns you to the first SELECT output row.
column
identifies a column to total. The column must be a column with a numeric data type
from the current detail line and cannot be an IF/THEN/ELSE column. You can
specify column as a column name, an alias, a detail alias, or COL number (which
specifies the position of the column in the select list).
Considerations—TOTAL
•
Each TOTAL command replaces the previous total command
Only one TOTAL command is in effect at a time. When you enter a TOTAL
command, it replaces the previous TOTAL command.
•
Total formats
A total prints immediately beneath the print item it totals and uses the same display
format as the print item.
When you specify a print item in a DETAIL command, make sure the display format
in the AS clause allows enough room for the total. If the total is too large for its
display format, the field is filled with overflow characters instead of a total.
To include a date-time value in an AS clause, use JULIANTIMESTAMP to convert
it to a Julian timestamp.
To define a sufficiently large total field for an INTERVAL total (which cannot use
an AS clause), specify a heading wide enough for the largest total value you expect.
Totals require three report lines: two for underline characters (see
UNDERLINE_CHAR Option on page U-1) and one for the totals.
•
Precision for calculations
In calculating totals, the report writer uses the maximum format for the item's data
type and the same scale as the item to be totaled. 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.
TOTAL column [, column ] ... ;