SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-28
Using Subqueries
Using the 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
preceding invoice. You can select information about orders with delivery dates prior to
June 1, 2000 by entering the following command:
>> 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/MP Reference Manual.
Using Subqueries
A subquery is a special form of the SELECT command. A subquery selects only for
purposes of comparison. You specify subqueries in predicates of a search condition in
the WHERE clause or the HAVING clause of a SELECT command.
A correlated subquery is evaluated for each row selected by the main query. A
subquery that does not contain a correlated reference is evaluated once. The result is
used for evaluating the WHERE clause against each row selected by the main query.
The following commands illustrate a query that does not contain a correlated
reference:
>> SELECT SUPPNUM, PARTNUM, PARTCOST
+> FROM INVENT.PARTSUPP
+> WHERE PARTNUM = 2003
+> AND PARTCOST > (SELECT MIN(PARTCOST)
+> FROM INVENT.PARTSUPP
Figure 3-7. Example of 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