SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-27
Using Views
information. The view columns are specified in parentheses following the view name.
The rest of the command specifies the selection of columns and rows for the view.
>> CREATE VIEW CUSTORD
+> ( CUSTNUM, CUSTNAME,
+> STREET, CITY, STATE, POSTCODE,
+> CREDIT,
+> ORDERNUM, ORDER_DATE, DELIV_DATE,
+> SALESREP,
+> PARTNUM, UNIT_PRICE, QTY_ORDERED )
+> AS SELECT
+> C.CUSTNUM, CUSTNAME,
+> STREET, CITY, STATE, POSTCODE,
+> CREDIT,
+> O.ORDERNUM, ORDER_DATE, DELIV_DATE,
+> SALESREP,
+> PARTNUM, UNIT_PRICE, QTY_ORDERED
+> FROM CUSTOMER C,
+> ORDERS O,
+> ODETAIL OD
+> WHERE C.CUSTNUM = O.CUSTNUM
+> AND O.ORDERNUM = OD.ORDERNUM ;
With the CUSTORD view defined, you can select data for an invoice by entering:
>> SELECT * FROM CUSTORD ;
To further simplify your task, you can specify in an EDIT file the set of report formatting
commands to print the invoice.
These commands define one version of an invoice and are stored in a file named
INVOICE. For explanations and examples of report formatting commands used in this
report, see Section 4, Customizing a Report.
SET LAYOUT RIGHT_MARGIN 65, PAGE_LENGTH 24;
PAGE TITLE 'INVOICE' CENTER;
REPORT TITLE 'Customer: ', CUSTNAME,
TAB 40, 'Order Date: ', ORDER_DATE AS I6,
SKIP 1,
TAB 11, STREET,
TAB 40, 'Deliv. Date: ', DELIV_DATE AS I6,
SKIP 1,
TAB 11,
CONCAT (CITY STRIP, ', ', STATE STRIP, SPACE 1,
POSTCODE),
TAB 40, 'Order No.', ORDERNUM;
DETAIL PARTNUM AS I6
HEADING 'Part No.',
UNIT_PRICE AS F8.2
HEADING 'Unit Price',
QTY_ORDERED AS I8
HEADING 'Quantity',
UNIT_PRICE * QTY_ORDERED AS M<$ZZZ,ZZ9.99>
HEADING 'Total' NAME TOTALPRICE ;
TOTAL TOTALPRICE;
REPORT FOOTING 'Terms 60 days net.';