SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-32
Developing Multistep Queries
When using subqueries, consider the following points :
•
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 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, but subqueries within the WHERE clause of another
subquery are at a different level.
Developing Multistep Queries
The techniques discussed next use interim 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/MX Reference Manual.
Figure 3-8. A 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