SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-14
Naming Select List and Detail Line Items
+> FIRST_NAME NOHEAD,
+> JOBCODE HEADING 'Job Code';
S> LIST FIRST 2;
Employee No. Job Code
------------ --------
1 GREEN ROGER 100
32 RUDLOFF THOMAS 100
S>
For more information about defining headings, see Specifying Column Headings on
page 4-17.
Naming Select List and Detail Line Items
To assign an alias name to items in the select list, use the NAME command. You can
then refer to that item by its alias in a DETAIL command or in any other report
formatting command. Alias names are useful for referring to expressions.
In this example, the alias name TOTAL_COST is assigned to COL 3, and the alias
name PROFIT is assigned to COL 4. The alias names are used in the DETAIL
command:
>> SELECT P.PARTNUM, PARTCOST,
+> PARTCOST * QTY_AVAILABLE,
+> QTY_AVAILABLE * (PRICE - PARTCOST)
+> FROM SAMDBCAT.SALES.PARTS P,
+> SAMDBCAT.INVENT.PARTSUPP PS,
+> SAMDBCAT.INVENT.SUPPLIER S,
+> WHERE P.PARTNUM = PS.PARTNUM
+> AND PS.SUPPNUM = S.SUPPNUM;
S> NAME COL 3 TOTAL_COST;
S> NAME COL 4 PROFIT;
S> DETAIL PARTNUM HEADING 'Part No.',
+> PARTCOST HEADING 'Unit Cost',
+> TOTAL_COST HEADING 'Total Cost',
+> PROFIT HEADING 'Profit';
You can refer to a select list column by an alias name in any part of your report
definition.
If you need to refer to a detail item that does not have a column name or number, you
can assign the item a detail alias name.
For example, the NAME clause in this DETAIL command defines the detail alias name
PROFIT:
>> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SAMDBCAT.SALES.PARTS P,
+> SAMDBCAT.INVENT.PARTSUPP PS,
+> SAMDBCAT.INVENT.SUPPLIER S,
+> WHERE P.PARTNUM = PS.PARTNUM AND PS.SUPPNUM = S.SUPPNUM;
S> DETAIL PARTNUM HEADING 'Part No.',
+> QTY_AVAILABLE HEADING 'Units',