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-23
Using Parameters With SELECT Commands
You should consider the following restrictions that apply to expressions depending on
where you specify them:
You can include aggregate functions in a select list expression but not in a DETAIL
command expression.
You can include report functions in a DETAIL command expression but not in a select
list expression. The report functions are LINE_NUMBER, COMPUTE_TIMESTAMP,
CURRENT_TIMESTAMP, and PAGE_NUMBER. However, you can use the NonStop
SQL/MP date-time functions both in the select list and in DETAIL command
expressions.
Using Parameters With SELECT Commands
You can use named parameters in a SELECT command to specify that a value will be
provided when the command is executed. For the complete syntax description of
named parameters, see the SQL/MP Reference Manual. To follow this example, you
should know that the form of a named parameter is:
?[ simple-name]
For example, ?CUSTNO is a parameter that passes a customer number to SQLCI:
SELECT * FROM SALES.CUSTOMER WHERE CUSTNUM = ?CUSTNO ;
Named parameters can be useful in a report definition that you specify in an EDIT file
and execute by using the OBEY command. To set the value, an operator enters a
command such as the following before executing the command file:
>> SET PARAM ?CUSTNO 324 ;
You can specify the value for a parameter as a numeric or string literal, or you can use
the CURRENT_TIMESTAMP or COMPUTE_TIMESTAMP functions to calculate the
Figure 3-6. Expressions in the Detail Line
S> LIST NEXT 2;
Part No. Unit Cost Total Cost Profit
212
244
2000.00
2400.00
7050000.00
10622400.00
1762500.00
2655600.00
>> VOLUME INVENT;
>> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S
+> WHERE P.PARTNUM = PS.PARTNUM AND PS.SUPPNUM = S.SUPPNUM;
S> DETAIL P.PARTNUM HEADING "Part No.",
+> PARTCOST HEADING "Unit Cost",
+> PARTCOST * QTY_AVAILABLE HEADING "Total Cost",
+> QTY_AVAILABLE * (PRICE - PARTCOST) HEADING "Profit";
VST0306.vsd