Specifying substitution variables

Substitution variables are used to enter changing values to a SQL query at run time.

About this task

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, form, or procedure) 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 can contain up to 18 alphabetic, numeric or special characters.

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.

In the following 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.

To use a substitution variable:

Procedure

  1. Open a query.
  2. Type this SQL statement: SELECT * FROM Q.STAFF WHERE DEPT = &MIN_DEPT
  3. Run the query. The Prompt Variables window opens.
  4. Type 50 in the Value field of the window.
  5. Click OK.
    The query will run with the value 50 for DEPT.

What to do next

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