DB2 graphic  QMF Version 8

Making your query reusable with substitution variables

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.

Figure 51. 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:

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:

Figure 52. RUN command 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

Figure 53. RUN Query command prompt
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.


Go to the previous page Go to the next page

Downloads | Library | Support | Support Policy | Terms of use | Feedback
Copyright IBM Corporation 1982,2004 Copyright IBM Corporation 1982, 2004
timestamp Last updated: March, 2004