Data Transformation Engine Database Interface Designer Reference Guide

Chapter 3 - Database/query Files Defining Variables in SQL Statements

Database Interface Designer Reference Guide

Defining Variables in SQL Statements

Elements of SQL statements can be executed as map sources that are determined

at runtime. Use the Database Interface Designer to define a statement variable

with a pseudo value in an SQL statement and then pass the actual value on the

command line at runtime. This technique is beneficial when using the

RUN function

because it allows one map to modify the SQL statement of another map or to

build, potentially, an entire SQL statement.

When you generate type trees using the Database Interface Designer, a

substitution value must be entered for each variable to ensure that the syntax of

the SQL statement is valid. The Database Interface Designer provides a facility for

specifying a value for these variables; however, the value you enter for a variable

in the Database Interface Designer does not have to be the same value passed at

runtime. (Any value can be passed.)

Defining a Query with Variables

In the Database Interface Designer Query dialog box, variables can be specified

in SQL statements as literals enclosed in pound sign (

#) characters. For example,

you might enter a statement that defines a variable named ID:

select * from BigTable where Identifier = #ID#

Because the value of a variable may be a text string, you can also create larger

elements of the statement variable. For example:

select * from BigTable where #WhereClause#

When you define variables in a query, the Database Interface Designer

automatically detects the presence of the variables in the statement and lists each

variable in the Navigator, along with a variable icon (

Use the Define Variables dialog box to enter the variable values. Note that you

cannot generate a type tree for the query until you have specified a value for each

variable it contains. Also, if the variable you are defining in the Define Variables

dialog box is a text string, you must enclose the value in single quotation marks.

Note The pseudo values specified using the Define Variables dialog box are used in

the Database Interface Designer only when accessing the database to generate

the type tree. They are not used when executing a map.