SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-13
Specifying the Items in a Detail Line
You can refer to columns LAST_NAME and FIRST_NAME as COL 2 and COL 3:
S> DETAIL col2, col3;
When you use a DETAIL command, consider these points:
•
You can specify only one DETAIL command at a time. You can edit the command
by using FC, replace the command by reentering it, or delete the command by
entering RESET REPORT DETAIL.
•
In report formatting commands, you can refer to the items of the select list by
column name or by column number
•
You can omit a correlation name when specifying a column in a report command if
the reference is unambiguous. For example, if D.DEPTNUM and E.DEPTNUM are
items in the select list, you must include the correlation name in report commands.
If DEPTNUM appears only once in the list as E.DEPTNUM, you can omit the
correlation name in report commands.
•
You can define a report and specify elements such as page titles, footings,
subtotals, and totals without specifying a DETAIL command. The default DETAIL
line is in effect at the select-in-progress prompt.
Use the DETAIL command when you want to do either of the following:
•
Omit select list columns from the output line. For example, you might need to
select a column for the purpose of ordering the rows, but you do not want the
column to appear in the report.
>> SELECT EMPNUM, LAST_NAME, FIRST_NAME, JOBCODE, SALARY
+> FROM EMPLOYEE
+> ORDER BY SALARY DESCENDING;
S> DETAIL EMPNUM, LAST_NAME, FIRST_NAME, JOBCODE;
S> LIST FIRST 2;
EMPNUM LAST_NAME FIRST_NAME JOBCODE
------ ---------------- --------------- -------
1 GREEN ROGER 100
32 RUDLOFF THOMAS 100
S>
•
Specify headings, spacing between items, multiple line entries, concatenation of
items, conditional printing of information, and other special handling of information.
To enhance the format of the information selected by the previous command, you can
enter a DETAIL command. For example, to override the default headings, enter:
S> DETAIL EMPNUM HEADING 'Employee No.',
+> LAST_NAME NOHEAD,
Note. When you refer to a select list column by number in a SELECT command clause
such as GROUP BY and ORDER BY, you specify only the number; for example, GROUP
BY1. When you refer to a select list column by number in a report formatting command,
such as BREAK ON, you specify COL number; for example, BREAK ON Col 2.