SQL/MP Report Writer Guide
Table Of Contents
- What’s New in This Manual
- About This Manual
- 1 Introduction to the NonStop SQL/MP Report Writer
- 2 Using SQLCI and the Report Writer
- 3 Selecting Data for a Report
- 4 Customizing a Report
- Defining the Layout
- Specifying the Items in a Detail Line
- Naming Select List and Detail Line Items
- Organizing Rows Into Break Groups
- Labeling Information
- Formatting Data Values
- Formatting Dates and Times
- Using TACL to Pass Parameters
- Conditional Printing of Items or Line Entries
- Redefining Special Characters
- Calculating Totals
- Calculating Subtotals
- Printing Double-Byte Characters
- A Comparison of the Report Writer and the Enform Language
- Index
Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-4
Developing a Query
for each column of the table. The table you specify must be on the current default
subvolume, or you must qualify the table name in the FROM clause, as shown:
>> SELECT * FROM SALES.PARTS;
To select the data needed for the report in Figure 3-1, you must join rows of the three
tables. First, consider the joining of rows from the PARTS and PARTSUPP tables that
have the same PARTNUM value:
>> VOLUME INVENT;
>> SELECT *
+> FROM SALES.PARTS, PARTSUPP
+> WHERE PARTS.PARTNUM = PARTSUPP.PARTNUM;
The VOLUME command makes INVENT the current default subvolume. If you omit the
VOLUME command, the PARTSUPP table name should be qualified as
INVENT.PARTSUPP.
The WHERE clause specifies how the rows are to be joined; in this example, rows with
the same part number in each table are joined. The expression in the WHERE clause
is called a comparison predicate. If you omit the WHERE clause, each row of the
PARTS table is joined with each row of the PARTSUPP table. Because this approach
results in an inefficient query; it is recommended that you include a WHERE clause
with a predicate to indicate how to join the tables in the FROM clause.
Figure 3-2 illustrates the result of joining these tables. Suppose PARTS contains two
rows and PARTSUPP contains five rows.
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
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