SQL/MP Report Writer Guide

Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-13
Naming Select List and Detail Line Items
S> DETAIL EMPNUM HEADING "Employee No.",
+> LAST_NAME NOHEAD,
+> 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 Column Headings on page 4-16.
Naming Select List and Detail Line Items
You can assign an alias name to any item in the select list by using 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 the following 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:
>> SET LIST_COUNT 0;
>> SELECT P.PARTNUM, PARTCOST,
+> PARTCOST * QTY_AVAILABLE,
+> QTY_AVAILABLE * (PRICE - PARTCOST)
+> FROM SALES.PARTS P, PARTSUPP PS, 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 the following
DETAIL command defines the detail alias name PROFIT:
>> SET LIST_COUNT 0;
>> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S
+> WHERE P.PARTNUM = PS.PARTNUM AND PS.SUPPNUM = S.SUPPNUM;
S> DETAIL PARTNUM HEADING "Part No.",
+> QTY_AVAILABLE HEADING "Units",
+> PARTCOST HEADING "Unit Cost",
+> PARTCOST * QTY_AVAILABLE HEADING "Total Cost",
+> QTY_AVAILABLE * (PRICE - PARTCOST)