SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-5
Selecting Columns of Source Data
Because the part number column has the same name in the PARTS table and the
PARTSUPP table, you must qualify the column name with the table name. You can use
the table name, which is the implicit correlation name (for example,
PARTS.PARTNUM). Alternatively, you can define an explicit correlation name in the
FROM clause to use as an abbreviation for qualifying column names. For example, to
define P and PS as explicit correlation names, enter:
>> SELECT *
+> FROM SAMDBCAT.SALES.PARTS P, SAMDBCAT.INVENT.PARTSUPP PS
+> WHERE P.PARTNUM = PS.PARTNUM;
Correlation names are required in some types of subqueries, discussed under Using
Subqueries on page 3-28.
To join the SUPPLIER table to the other two tables, join the rows of the result table
shown in Figure 3-2 to rows of the SUPPLIER table that have the same SUPPNUM
value:
>> SELECT *
+> FROM SAMDBCAT.SALES.PARTS P,
+> SAMDBCAT.INVENT.PARTSUPP PS,
+> SAMDBCAT.INVENT.SUPPLIER S,
+> WHERE P.PARTNUM = PS.PARTNUM
+> AND PS.SUPPNUM = S.SUPPNUM;
If the SUPPLIER table is also on the default schema, you need not qualify the name.
Otherwise, qualify the name with the correct catalog and schema, as shown.
Selecting Columns of Source Data
You can specify the content of the detail lines of a report in the SELECT command
select list. If you want to retrieve values for use in the report but do not want the values
to appear in the detail lines, you specify both a select list and a DETAIL command.
Figure 3-2. Sample Rows from Joined Tables
PARTDESC PRICE
PARTNUM QTY_AVAILABLE
PARTNUM
SUPPNUM
PARTCOST
QTY_RECEIVED
PARTS Table
PARTSUPP Table
212
212
4102
4102
4102
PC S
. .
PC S . .
DISK . .
DISK . .
DISK . .
2500.00
2500.00
28.00
28.00
28.00
3525
3525
6540
6540
6540
212
212
4102
4102
4102
1
3
6
8
15
2000.00
1900.00
20.00
19.00
21.00
20
35
115
140
30
VST0302.vsd