SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-28
Using Subqueries
To print an invoice, enter:
>> SET LIST_COUNT 0;
>> SELECT * FROM RWVIEWS.CUSTORD
+> WHERE CUSTNUM = 1234 AND ORDERNUM = 100210;
S> OBEY INVOICE;
Figure 3-7 shows a sample invoice (line numbers do not appear on the printed report).
Using a view saves time if you want to define several reports based on the same data.
For example, you might have a summary report that uses the same data as the invoice
in Figure 3-7. You can select information about orders with delivery dates prior to June
1, 2000 by entering:
>> SELECT * FROM RWVIEWS.CUSTORD
+> WHERE DELIV_DATE < 000601 ;
You can then print a report using the retrieved data.
For more information about views, see the SQL/MX Reference Manual.
Using Subqueries
A subquery is a special form of the SELECT command that selects only for purposes
of comparison. You specify subqueries in the predicates of a search condition in the
WHERE clause or the HAVING clause of a SELECT command. Subqueries cannot be
used inside conditional expressions.
Figure 3-7. An Invoice
INVOICE
Order Date : 870410
Deliv. Date : 870410
Order No. 100210
Customer : DATASPEED
300 SAN GABRIEL WAY
NEW YORK, NEW YORK 10014
$ 10,500.00
Terms 60 days net.
Part No.
244
2001
2403
5100
3500.00
1100.00
620.00
150.00
Unit Price
Quantity
3
3
6
10
Total
$ 3,300.00
$ 3,720.00
$ 1,500.00
$ 19,020.00
VST0307.vsd