Data Transformation Engine Functions and Expressions Reference Guide

Chapter 3 - Function Reference DBQUERY
Functions and Expressions Reference Guide
93
In another example, assume that you have an input file containing one order
record. To map that order to another proprietary format, you also have a parts
table with pricing information for every part for every customer, a very large
table. Rather than using the entire parts table as the input to your map, you
might use the
RUN function with a DBQUERY to dynamically select only those
rows from the parts table corresponding to the customer in the order file, as
follows:
RUN ( "MapOrder.MMC" ,
"IE2" + DBQUERY ( "SELECT * FROM Parts WHERE CustID = "
+ CustomerNo:OrderRecord:OrderFile + " ORDER BY PartNo"
,"PartsDB.MDQ", "PartsDatabase" ) )
Uses
Use
DBQUERY to execute an SQL statement when you want to look up
information in a database using a parameterized query that is based on
another value in your data.
If your SQL statement is a SELECT statement, the
DBQUERY function may be
used in conjunction with the
RUN function to issue dynamic SELECT statements
whose results can be used as input to another map.
Use Meaning 2 of the
DBQUERY function to execute an SQL statement when the
database, table, or other database parameters might vary; perhaps being
supplied by a parameter file.
Related Functions
DBLOOKUP, EXTRACT, FAIL, LASTERRORCODE, LASTERRORMSG, LOOKUP,
SEARCHUP, SEARCHDOWN, VALID