SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-86
SUBTOTAL Command

This example returns “”:
SUBSTRING ("Robert John Smith" FROM -5 FOR 2)

This example returns “Ro”:
SUBSTRING ("Robert John Smith" FROM -2 FOR 5)

This example produces an empty string, “”, which is different than a null value for
the result:
SUBSTRING("Robert John Smith" FROM 8 FOR 0)
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

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.

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.

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
SUBTOTAL column [, column ] ... [ OVER break-col ] ;