DB2 graphic QMF Version 8

Substitution variables

Substitution variables are used in QMF objects for substituting variables to strings at run time. This feature enables you to substitute a part of an SQL statement and make it more generic. Substitution variables are active only while the object (query, procedure or form) is running. As a result, only one object can access the substitution variable. The variable will not exist after the object is executed.

A substitution variable is special text in a query that begins with an ampersand character (&). A substitution variable and can contain up to 18 alphabetic, numeric or special characters such as ^ ! $ ~ { } ? @ # % \ or _.

A substitution variable can appear anywhere in a query. The value of the substitution variable can be anything used in a query (except a comment). For example, you can use a substitution variable in place of a column name, search condition, subquery, or any specific value.

Example

You will be prompted for a customer number each time you run the following query:

SELECT ORDERNO, SALESREPNO, PRODNO, QUANTITY, &CUSTNO AS CUSTOMER# 
FROM Q.SALES

When you run the query and supply customer number at the prompt, the query will retrieve only those records that are associated with the specified customer number. Later you can launch the query and provide a different customer instead of writing a separate query.

Task

The following sample query illustrates how to use a substitution variable:

  1. Open a query.
  2. Type this SQL statement:
    SELECT * FROM Q.STAFF WHERE DEPT >= &MIN_DEPT
  3. Select Query--> Run.

    The Enter Substitution Variable Values dialog box opens.

  4. Type 50 in the Value field.
  5. Click OK.

Try experimenting with substitution variables by replacing values in the SELECT and FROM clauses. See what results your queries return.


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