Data Transformation Engine Database Interface Designer Reference Guide

Chapter 7 - Database Functions Using Bind Values in Database Functions
Database Interface Designer Reference Guide
118
of the
DBLOOKUP function was called ItemData, the rule using the vendor ID
column would be:
=WORD ( ItemData , "|" , 2 )
The values of the other columns can be retrieved in a similar manner.
Uses
Refer to the following situation for an example of when you might want to use
DBLOOKUP to execute an SQL statement:
♦ when you want to retrieve a single column value from a database based upon
another value in your data without the carriage return/line feed row terminator
Refer to the following situations for examples of when you might want to use
DBQUERY to execute an SQL statement:
♦ to execute an SQL statement when you want to look up multiple column values
for multiple rows in a database using a parameterized query based upon
another value in your data
♦ when your SQL statement is a SELECT statement, the
DBQUERY function may be
used along with the
RUN function to issue dynamic SELECT statements whose
results can be used as input to a map.
♦ when using other SQL statements such as
INSERT, UPDATE, DELETE, and so on
For information about functions such as
EXTRACT, LOOKUP, SEARCHUP,
SEARCHDOWN, GET, and PUT, refer to the Functions and Expressions Reference
Guide.
Using Bind Values in Database Functions
Note For information about the availability and usage of the bind facility for your
specific database and platform, refer to the database-specific adapter reference
guides.
When the Database Management System (DBMS) receives an SQL request, the
request is cached because many applications repeatedly issue the same SQL
statement. If the SQL statement differs from one that the DBMS has recently
processed, the statement is reevaluated. (The DBMS performs parsing, derives an
execution plan, and so on.)
Similarly, if a
DBLOOKUP or DBQUERY function repeatedly issues the same
statement, the second and subsequent executions of the statement execute much
faster than the first execution. However, if any element of the statement varies,