Data Transformation Engine Database Interface Designer Reference Guide

Chapter 7 - Database Functions Using DBLOOKUP and DBQUERY
Database Interface Designer Reference Guide
116
Also assume that this database has been defined in a file named mytest.mdq
using the Database Interface Designer. The name of the database, as specified in
the .mdq file, is PartsDB.
Using the Syntax1 format, the following
DBQUERY function:
DBQUERY ("SELECT * from PARTS", "mytest.mdq", "PartsDB")
returns:
1|¼" x 3" Bolt<cr/lf>2|¼" x 4" Bolt<cr/lf>
where <cr/lf> is a carriage return followed by a line feed.
Notice that if the same function was executed using
DBLOOKUP, the results would be:
1|¼" x 3" Bolt<cr/lf>2|¼" x 4" Bolt
The difference between the two results is that the final carriage return/line feed is
stripped off the end of the results of the
DBLOOKUP function.
Using Syntax2, you can obtain the same results as in the previous
DBQUERY
function as shown in the following examples:
DBQUERY ("SELECT * from PARTS where PART_NUMBER =1",
"-MDQ mytest.mdq -DBNAME PartsDB")
or:
DBQUERY ("SELECT * from PARTS where PART_NUMBER =1",
"-DBTYPE ORACLE -CONNECT MyDatabase -USER janes
-PASSWORD secretpw")
Example 3 - Using DBQUERY to Provide Map Input to RUN Function
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 include the
RUN function with your Syntax1-formatted 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") +"'")