SQL Script Substitution Variables

Substitution variables provide an easy way to execute scripts in situations when you need to run the same SQL queries multiple times or periodically using a different set of parameters for each script execution, for example during application tests or when running system reports. Using variables in these situations eliminates a task of modifying SQL syntax every time you need to provide a new parameter value, such as report dates or customer code. This feature is especially valuable when the same parameter is used in SQL script in multiple places and the value of this parameter must be edited every time in all places. Without usage of script variables, there is a high probability of error – not only a syntax error but also an error of getting incorrect SQL query result.

Substitution variables can be used in SQL scripts in place of constants, names of columns or tables. If a SQL script contains any substitution variables, the dialog box prompting for values opens automatically when a user attempts to execute this script. To define a variable in the script, just type the variable in the actual SQL/SPL statement in the following format:

${varname} - where 'varname' is any user defined name.

Example 1:

SELECT * FROM tbl_factoryorders where orderamount > ${largeorder}

Example 2:

SELECT * FROM ${salestable} where useraccountno = '${accountnumber}'

The same script variable can be used in different SQL statements if it represents the same logical value. If you use a variable in more than one place in the script, you will be prompted for its value only once and the entered value is applied to substitute the variable in all places. The Preview panel, located below the variable grid, shows all SQL statements where the selected variable is used. After you enter a value for the variable, the preview panel shows the entered value instead of a variable name so that you could verify that the resulting SQL syntax is valid. SQL statements with substituted variables are sent to an Informix server exactly the same way as you see them in the Preview panel.