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-5
Developing a Query
FROM clause to use as an abbreviation for qualifying column names. For example, the
following command defines P and PS as explicit correlation names:
>> SELECT *
+> FROM SALES.PARTS P, PARTSUPP PS
+> WHERE P.PARTNUM = PS.PARTNUM;
Correlation names are required in some types of subqueries. These subqueries are
discussed in Using Subqueries on page 3-28.
To join the SUPPLIER table to the other two tables, you can join the rows of the result
table shown in Figure 3-2 to rows of the SUPPLIER table that have the same
SUPPNUM value. The way to express this relation is as follows:
>> SELECT *
+> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S
+> WHERE P.PARTNUM = PS.PARTNUM
+> AND PS.SUPPNUM = S.SUPPNUM;
The SUPPLIER table is also on the INVENT subvolume, so the name does not need to
be qualified.
Using DEFINEs
For queries that you intend to execute many times, you should use DEFINE names in
the FROM clause. For example, you can use the DEFINE name =PARTS in the FROM
clause. Assume the following command is in the command file named FINDPART:
SELECT PARTNUM, PARTDESC, QTY_AVAILABLE FROM =PARTS;
Before you execute the query, you specify the actual file name that corresponds to
=PARTS by using an ADD DEFINE command such as the following:
>> ADD DEFINE =PARTS, FILE $WAREHS1.SALES.PARTS;
>> OBEY FINDPART;
The query searches the PARTS table on subvolume $WAREHS1.SALES.
DEFINEs allow you to use more readable names in SELECT commands, and they
provide location independence. For more information about using DEFINEs, see the
SQL/MP Reference Manual.
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.
The select list must specify each column of data used in the report. For example, a
column value you print in a title or footing does not have to appear in the detail line, but
it must be retrieved in the select list.