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-31
Using Subqueries
VOLUME SALES;
SET LIST_COUNT 0;
SELECT X.PARTNUM,
PARTDESC,
ORDERNUM,
QTY_AVAILABLE,
QTY_ORDERED
FROM PARTS, ODETAIL X
WHERE PARTS.PARTNUM = X.PARTNUM
AND (?PERCENT/100) * QTY_AVAILABLE
< ( SELECT SUM(QTY_ORDERED)
FROM ODETAIL
WHERE X.PARTNUM = ODETAIL.PARTNUM)
ORDER BY X.PARTNUM;
DETAIL PARTNUM AS I4 HEADING "PART",
PARTDESC,
QTY_AVAILABLE AS I6 HEADING "AVAILABLE",
ORDERNUM AS I6 HEADING "ORDER NO.",
QTY_ORDERED AS I5 HEADING "ORDERED";
BREAK ON PARTNUM, PARTDESC, QTY_AVAILABLE;
SUBTOTAL QTY_ORDERED OVER QTY_AVAILABLE;
To execute this query using a value of 5 percent, enter:
>> SET PARAM ?PERCENT 5;
>> OBEY ORDERPCT;
>> LIST ALL;
The report in Figure 3-8 shows that part numbers 2001 and 6400 satisfy the conditions
of the query.
If you do not specify OVER QTY_AVAILABLE in the SUBTOTAL command, the
subtotals will be calculated when any break column value changes. In this example,
the values in all three break columns change at the same time. Thus, printing the
subtotals for each break column is redundant.