When you specify substitution variables in an SQL query, you can use the same query to retrieve different information by supplying a new value for the variable each time you run the query.
The following query selects department data. By using a substitution variable (&DEPARTMENT) for the department number in the row condition, you can specify a different department number each time you run the query.
SELECT ID, NAME, JOB, SALARY FROM Q.STAFF WHERE DEPT=&DEPARTMENT
You can specify values for substitution variables in any of the following ways:
To specify a value as part of the RUN command
For example, to specify a value for the &DEPARTMENT variable, on the QMF command line, enter:
RUN QUERY (&DEPARTMENT = 38
Enclose the value in parentheses if it contains one of the following special characters:
For example:
RUN QUERY (&X=(DEPT,NAME,SALARY)
To specify text for a variable, just type the text. You might have to enclose the text with quotes, depending on whether it would require quotes if you entered it directly into the query. For example, the following query has two variables. For the first you specify a column name as the value; for the second, you specify text that contains a quotation mark.
SELECT &X FROM Q.STAFF WHERE NAME=&Y
If the text itself contains quotation marks, add another set of quotation marks for each quotation mark:
RUN QUERY (&X=SALARY, &Y='O''BRIEN'
To specify a value on the RUN Command Prompt panel: If your query contains a variable, and you do not specify a value for the variable when you type the RUN command, the RUN Command Prompt panel displays.
The prompt panel displays the variables that need values. Type the values for the variables.
+--------------------------------------------------------------------------------+ | RUN Command Prompt -- Values of Variables | | | | Your RUN command runs a query or procedure with variables that need | | values. Fill in a value after the arrow for each variable named below: | | 1 to 10 of 10 | | &DEPARTMENT 38__________________________________________________ | | ____________________________________________________ | | ____________________________________________________ | | ____________________________________________________ | | ____________________________________________________ | +--------------------------------------------------------------------------------+
To specify values for substitution variables using global variables: You can define global variables with the SET GLOBAL command. A global variable keeps its value until you reset it, or until you end the QMF session.
For example, to set a global variable value for the &DEPARTMENT variable, on the QMF command line, enter:
SET GLOBAL (DEPARTMENT=38
You can specify up to 10 variable values. Separate the values with commas or with blanks.
For more information on defining global variables, see the QMF Reference, SC26-4716-05 .