When you specify substitution variables in a prompted 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 prompted query in Figure 50 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.
Figure 50. This query uses a substitution variable for the DEPT name.
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | | | Tables: | | Q.STAFF | | | | Columns: | | ID | | NAME | | JOB | | SALARY | | | | Row Conditions: | | If DEPT Is Equal To &DEPARTMENT; | +--------------------------------------------------------------------------------+
You can enter substitution variables on any Prompted Query panel where you can enter expressions.
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:
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 variables that need values appear on the panel. 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 either commas or blanks.
For more information on defining global variables, see the QMF Reference, SC26-4716-05 manual.