SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-32
Developing Multistep Queries
Consider the following points when using subqueries:
•
A subquery must be enclosed in parentheses.
•
The select list can be a single expression, an asterisk (*), or a correlation name
followed by an asterisk. You cannot specify an asterisk in any predicates except
EXISTS unless the FROM clause of the subquery refers to a single table or view
consisting of a single column.
•
A subquery in a comparison predicate must result in a single value; either one
column of one row must satisfy the search condition or the select list element must
be an aggregate function.
A subquery in an EXISTS, IN, or quantified predicate can have more than one row
that satisfies the search condition.
•
You can nest subqueries up to 16 levels. Subqueries within the same WHERE
clause are at the same level. A subquery within the WHERE clause of another
subquery is at a different level.
Developing Multistep Queries
The following techniques use temporary tables to select data based on more than one
query. To create a table, you use the CREATE TABLE statement. You must have
access to a catalog to define a table, or you must have the authority to create your own
catalog. For information about the requirements for creating tables, see the CREATE
TABLE statement in the
SQL/MP Reference Manual.
Figure 3-8. Example of Report With a Subquery
6400 STREAMING TAPE,M20 2100 200320
300350
400410
800660
7
5
70
30
112
*
2001 GRAPHIC PRINTER,M1 2100 3
10
36
16
60
30
155
*
100210
200300
400410
500450
600480
800660
PART PARTDESC AVAILABLE ORDER NO. ORDERED
VST0308.vsd