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 51 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.
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:
For example:
RUN QUERY (&X=(DEPT,NAME,SALARY)
Type the text to specify a variable. You might have to enclose the text with quotes. For example, the following query has two variables. For the first variable, 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__________________________________________________ ____________________________________________________ ____________________________________________________ ____________________________________________________ ____________________________________________________
In DB2 QMF Version 8.1, the RUN command supports long owner and table names. The RUN command prompt panel now allows for the entry of long names for tables and views. The Name entry field is increased from 50 to 280 bytes which will accommodate an object name of the form: "location(16)"."authid(128)"."object name(128)". Below are examples of the new RUN command prompt screen and the RUN QUERY prompt screen:
DXYEPRUN RUN Command Prompt 1 to 8 of 8 Type ( ) Name (<---------------- 50 bytes -------------------->)+ .... (<-----------------50 bytes -------------------->)+ .... (<---------------- 50 bytes -------------------->)+ .... (<---------------- 50 bytes -------------------->)+ .... (<---------------- 50 bytes -------------------->)+ .... (<---------------- 50 bytes -------------------->)+ To run an object from temporary storage, enter ist type: QUERY or PROC. To run an object from the database, enter its name (and optionally its type). Type can be QUERY or PROC. F1=Help F3=End F4=List F7=Backward F8=Forward
DXYEPRU3 RUN QUERY Command Prompt 1 to 20 of 20 Form (<---------------- 50 bytes -------------------->)+ (<-----------------50 bytes -------------------->)+ (<---------------- 50 bytes -------------------->)+ (<---------------- 50 bytes -------------------->)+ (<---------------- 50 bytes -------------------->)+ (<---------------- 50 bytes -------------------->)+ Enter the name of a specific form to be used in displaying a report. It can be FORM to use the contents of the temporary storage area, or it can be the name of a form in the database. If you omit it, a default form will be created and used. Confirm ( YES ) Display the conirmation panel before completing a query that inserts, updates, deletes rows, or drops objects in the database? YES or NO. Rowlimit ( ) Enter a number for the most rows to return from your query. If you leave this value blank, then all rows are returned. F1=Help F3=End F4=List F7=Backward F8=Forward
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 DB2 QMF Reference.