SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-26
Using Views
To prepare the SELECT command enter the following command:
>> OBEY SELPREP;
( PREPARE command is displayed here.)
.
.
--- SQL command prepared.
To execute the prepared SELECT command for each report and print the report, enter
the following command:
>> OBEY PRINTREP;
Using Views
A view is a logical table derived by projecting a subset of the columns or selecting a
subset of the rows from one or more tables or from another view. You specify the
definition of the view by using a SELECT command.
By defining a view, you can provide a way in which other users can retrieve values
without specifying complicated queries. For example, the following commands define a
view named CUSTORD to be used to print invoices and reports that summarize order
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.
>> VOLUME SALES;
>> 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 SALES.CUSTOMER C,
+> SALES.ORDERS O,
+> SALES.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 a
simple SELECT command:
>> SELECT * FROM SALES.CUSTORD ;