You can write a procedure that sets REXX variable values and passes these values to your template SQL statement. The QMF procedure in Figure 160 passes the substitution variable values to the query on the RUN QUERY command.
Figure 160. The procedure passes values on the RUN QUERY command.
/* REXX PROC */
IF DATE('W') = 'Friday' THEN
DO
sel = '(NAME, JOB, SALARY, COMM)'
con1 = "((SALARY > 15000) OR (JOB = 'MGR'))"
END
ELSE
DO
sel = '*'
con1 = '(DEPT=51)'
END
"RUN QUERY SENIORSTAFF (&&SELECT1 ="sel",&&COND1 ="con1
|
Because this procedure assigns values to the substitution variables (SELECT1 and COND1) on the RUN QUERY command, you must use a double ampersand before the variable names to tell REXX that these variables are assigned in the procedure, but not used in the procedure.
If you use only one ampersand before the variable name, as in this statement:
"RUN QUERY (&SELECT1 ="sel",&COND1 ="con1
QMF assumes that the variables are procedure variables, rather than variables to be passed to the query, and prompts you for their values when you run the procedure.
In the following lines from this procedure, the procedure assigns a character string to a REXX variable:
con1 = "((SALARY > 15000) OR (JOB = 'MGR'))"
con1 = '(DEPT=51)'
These values are then passed to the query on the RUN QUERY command. The values in the first REXX variable assignment, SALARY and JOB, are enclosed in double parentheses because the character strings passed to the query contain single parentheses and an equal sign. For the complete rules about using parentheses around character strings that are passed on a RUN command, see the QMF Reference, SC26-4716-05.
When you run this procedure on a Friday, the procedure sets the substitution variables and passes the values to the query so that QMF runs the following query:
SELECT NAME, JOB, SALARY, COMM FROM Q.STAFF WHERE (SALARY > 15000) OR (JOB='MGR')
If you run this procedure on any day other than Friday, QMF runs the following query:
SELECT * FROM Q.STAFF WHERE DEPT = 51