SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-25
Preparing a SELECT Command
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 before executing the command file. For example:
>> 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
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.
Suppose that a SELECT command in a command file named SALESUM consists of
these commands:
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;
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/MX 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. Use this approach to produce several
reports with the same SELECT command but based on data selected by different
criteria.
With preparation you also can use the EXPLAIN command to determine system
resources required to execute the query. For descriptions of the PREPARE,
EXECUTE, and EXPLAIN commands, see the SQL/MX Reference Manual.
For example, suppose that you want to print information about the orders from a
customer assigned to a specific sales representative. This report is produced each
month for sales representatives and all their customers. You can create a command
file that contains a command to prepare the SELECT command.