Making your SQL query reusable with substitution variables

When you specify substitution variables in an SQL query, you can use the same query to retrieve different information. You supply a new value for the variable each time you run the query.

You use substitution variables in an SQL query in the same way you use them in a prompted query. Though that topic shows prompted query examples, the concepts of using substitution variables are the same.

The following query 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.

SELECT ID, NAME, JOB, SALARY
FROM Q.STAFF
WHERE DEPT=&DEPARTMENT

For example, you can enter the name of the variable and its value as a parameter on the RUN QUERY command, as shown here:

RUN QUERY (&DEPARTMENT = 38

If the query includes multiple SQL statements, any variable value that you supply for the query applies to all SQL statements in the query that include a variable with that name.