Example 4 - using WORD to parse multi-column output from DBQUERY

Edit online

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. see 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 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.