SQL/MP Report Writer Guide

Using SQLCI and the Report Writer
HP NonStop SQL/MP Report Writer Guide—527213-001
2-16
Defining Reports in Command Files
Saving Report Commands in an Command file
To create a report and test the results as you work, follow these steps:
1. If you want to enter report formatting commands, you must prevent the entire
SELECT command output from displaying immediately. To do this, set the
LIST_COUNT option to a number less than the number of rows that will be
retrieved. If you do not know how many rows will be selected, set LIST_COUNT to
0:
>> SET LIST_COUNT 0;
>>
2. Enter the SELECT command. If you specify the SELECT command correctly, the
S> prompt appears. If you have made any errors, an error message appears. Use
the FC command to correct any errors in the command.
>> SELECT O.ORDERNUM, ORDER_DATE, CUSTNUM,
+> PARTNUM, UNIT_PRICE, QTY_ORDERED
+> FROM SALES.ORDERS O, SALES.ODETAIL OD
+> WHERE O.ORDERNUM = OD.ORDERNUM
+> ORDER BY CUSTNUM, O.ORDERNUM, ORDER_DATE, PARTNUM;
S>
3. Enter one or more report formatting commands. For example, you might want to
define the detail line. In a DETAIL command you can specify a subset of the select
list, rearrange the select list items, and specify expressions based on select list
items. You can add other commands such as BREAK ON.
S> DETAIL O.ORDERNUM,
+> PARTNUM,
+> UNIT_PRICE,
+> QTY_ORDERED,
+> UNIT_PRICE*QTY_ORDERED;
S> BREAK ON O.ORDERNUM ;
S> LIST FIRST 5;
ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED (EXPR)
---------- ------- ------------ ----------- -----------------
---
200320 5504 165.00 5 825.00
6201 195.00 16 3120.00
6301 245.00 6 1470.00
6400 540.00 7 3780.00
200490 3210 715.00 1 715.00
S>