Data Transformation Engine Database Interface Designer Reference Guide

Chapter 7 - Database Functions Using DBLOOKUP and DBQUERY
Database Interface Designer Reference Guide
117
Example 4 - Using WORD to Parse Multi-Column Output from DBQUERY
In certain situations, you may want to use one of the database functions, rather
than a database source, due to the size of a cross-reference table. However, you
need the function to return the data from several different columns.
For example, assume you need to create a map that processes inventory requests,
one order at a time, using a messaging system. Within the transformation of your
data, you need to reference the item master table that contains hundreds of
thousands of rows. However, for each item within the inventory request, you need
to get the internal item number, vendor ID, and description column value. You can
select from several available options, such as:
♦ Use a database source.
Define a query for only those columns needed. Use this for a database source
that you can then use within a
LOOKUP, EXTRACT, or SEARCHUP/SEARCHDOWN
function. However, due to the size of the item master table, this might mean
validating hundreds of thousands of rows to find the item information for only a
few items.
♦ Use multiple DBLOOKUP functions.
Assuming that the internal item number, vendor ID, and description column
values are going to be used within different outputs, you could use three
separate
DBLOOKUP functions to get the appropriate column value for each
item. However, this means executing three SQL statements to access the same
row within the item master table.
♦ Use the
DBLOOKUP and WORD functions.
Assuming that a functional map will be used to build an object containing the
three desired columns, a
DBLOOKUP could be used as an argument to the
functional map that retrieves the desired column values. Refer to the following
example:
=F_MakeOne ( Item Set:SomeInput ,
DBLOOKUP ( "SELECT INT_ITEM_NO, VENDOR, ITEM_DESC " +
"FROM ITEM_MASTER WHERE ITEM_NO = '" +
CatalogID:.:SomeInput + "'" , "PRODXL.MDQ"
"WDDM" ) )
The functional map F_MakeOne has two inputs: an Item Set and a text item
(that is the result of the
DBLOOKUP function). The text item will contain the three
column values separated by the pipe character (|). An example follows:
ARQJ06X6|DFQCO|6' Jump Rope
Then, each rule requiring one of these pieces of data will use the
WORD function to
access the appropriate column's data. For example, if the input card for the results