SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-24
Preparing a SELECT Command
value. You cannot use the CURRENT function or any other date-time functions to
specify a parameter value.
You cannot refer to parameters in a report formatting command. For another method of
passing values to commands, see Using TACL to Pass Parameters on page 4-44.
Suppose a SELECT command in an command file named SALESUM consists of the
following commands:
SET LIST_COUNT 0;
VOLUME SALES;
SELECT P.PARTNUM,
SUM (QTY_ORDERED),
SUM (UNIT_PRICE * QTY_ORDERED)
FROM PARTS P, ORDERS R, ODETAIL OD
WHERE R.ORDERNUM = OD.ORDERNUM AND OD.PARTNUM = P.PARTNUM
AND ORDER_DATE BETWEEN 870101 AND ?TODAY
AND P.PARTNUM BETWEEN ?FIRSTPART AND ?LASTPART
GROUP BY P.PARTNUM
ORDER BY P.PARTNUM;
LIST ALL;
To execute this query and print the default report, enter these commands:
>> SET PARAM ?FIRSTPART 2001,
+> ?LASTPART 5110,
+> ?TODAY 870811;
>> OUT_REPORT $S.#PRINTER;
>> OBEY SALESSUM;
The printed report appears as follows:
PARTNUM (EXPR) (EXPR)
------- -------------------- --------------------
2001 125 126900.00
2002 46 108000.00
2003 40 126900.00
. . .
. . .
. . .
5110 12 6300.00
You can display the current values of named parameters by using the SHOW PARAM
command.
In a SELECT command, you can also use unnamed parameters if you plan to prepare
the command before executing it. For descriptions of the SET PARAM and SHOW
PARAM commands, see the SQL/MP Reference Manual.
Preparing a SELECT Command
You can use the PREPARE command to compile a SELECT command and then use
the EXECUTE command to execute the query. This preparation is useful when you
intend to produce several reports with the same SELECT command but based on data
selected by different criteria. Preparation also allows you to use the EXPLAIN