SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-24
Using Parameters With SELECT Commands
Consider these 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
°
PAGE_NUMBER
•
You can use SQL/MX date-time functions in the select list only.
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/MX Reference Manual. The form of a named
parameter is:
?[ simple-name]
For example, ?CUSTNO is a parameter that passes a customer number to MXCI:
SELECT * FROM CUSTOMER WHERE CUSTNUM = ?CUSTNO ;
Figure 3-6. Expressions in the Detail Line
Part No. Unit Cost Total Cost Profit
212
244
2000.00
2400.00
7050000.00
10622400.00
1762500.00
2655600.00
>> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SAMDBCAT.SALES.PARTS P,
+> 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
S> LIST NEXT 2;
+> SAMDBCAT.INVENT.PARTSUPP PS,
+> SAMDBCAT.INVENT.SUPPLIER S