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.
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.
The following sample query illustrates how to use a substitution variable:
SELECT * FROM Q.STAFF WHERE DEPT >= &MIN_DEPT
The Enter Substitution Variable Values dialog box opens.
Try experimenting with substitution variables by replacing values in the SELECT and FROM clauses. See what results your queries return.