DataMigrator now allows you to build a single data flow that performs lookups into multiple tables using the DB_LOOKUP function without requiring explicit joins.

In the following example, DMORD is the primary source containing orders data, while DMCOMP and DMINV are the lookup sources. While DMORD contains store codes and product numbers, DMCOMP contains the store names for those store codes, and DMINV contains the product names for each product code.

Refer to the sample data flow DMLKUPS for the complete example.

For more information on improving lookup performance, see Improving Lookup Performance.

This example uses the DB_LOOKUP function to create a source transformation.

1. In the DMC, right-click an application directory in the navigation pane and choose New, then Flow. The Data Flow tab opens in the workspace, with the SQL object displayed.
2. Drag the data source object DMORD from the ibisamp application directory in the navigation pane into the workspace, to the left of the SQL object.
3. Double-click the DMORD source object or right-click it and select Source Transformations.

   The Source Transformations window opens.

4. Click the Insert Transforms button.

   The Transformation Calculator window opens.

5. Click the Functions tab, expand the Data Source and Decoding folder, and double-click the DB_LOOKUP function.

   The prototype for DB_LOOKUP appears in the Expression window and the Lookup window opens, as shown in following image.

   

6. Select a synonym to use for the lookup by clicking the ellipsis button after the Lookup Synonym field.

   The Select Synonym dialog box opens.

7. Select the DMCOMP synonym and click Select, as shown in following image.

   

8. From the Lookup window, click the Add/Delete button.
9. A lookup is similar to a join in that it uses columns from two different tables. Select STORE_CODE in the Source and Lookup Columns lists, and click the equal sign (=).

   Note: If you have Automatically add join conditions checked in Tools/Options/Data Management, the Lookup is added automatically since both data sources have a column with the same name and format and that column is a key column in the lookup table.

   When you have selected the columns, the Lookup window should appear as shown in following image.

   

10. Click OK to close the Lookup window.
11. Click the ellipsis button after the Return Column field.

    The Lookup columns window opens, as shown in following image.

    

12. Select STORENAME and click OK. Note that the column format is A20.

    The Lookup window should appear as shown in following image.

    

13. Click OK.

    The expression in completed in the Transformation Calculator window.

14. Enter STORENAME in the Column field and A20 in the Format field, to match the format of the selected return value.

    The Transformation Calculator should now appear as shown in the following image.

    

15. Click OK.

    The STORENAME source transformation is added to the Source Transformations window.

16. Repeat the process starting in Step 4 to add another lookup, using the parameters listed and described in the following table:

    Parameter	Column Name
    Lookup Synonym	DMINV
    Source Column	PROD_NUM
    Lookup Column	PROD_NUM
    Return Value	PRODNAME
    Column	PRODNAME
    Format	A30

    When you are done, the Source Transformations window should appear as shown in following image.

    

17. Click OK to close the Source Transformations window.
18. Right-click the SQL object and select Column Selection.

    The Column Selection window opens.

19. Select STORENAME, PLANT, PRODNAME, QUANTITY, and LINEPRICE in the Available Columns list, and click the > arrow to add them into the Selected Columns list.
20. Select STORENAME, PLANT, and PRODNAME in the Selected Columns list, and select Group By from the Aggregate drop-down menu.
21. Select QUANTITY and LINEPRICE in the Selected Columns list, and select Sum from the Aggregate drop-down menu.

    The Columns Selection window should appear as shown in following image.

    

22. Click OK to close Column Selection window.
23. Right-click the SQL object and select SQL Statement.

    The Select Statement window opens, as shown in following image. Note that there are no joins in the SQL statement.

    

24. Click the Test SQL button to test the generated SQL.

    The Test SQL Statement window opens, as shown in following image. Note that the answer set includes columns from DMCOMP (STORENAME), DMORD (PLANT, QUANTITY, LINEPRICE), and DMINV (PRODNAME). The sum of QUANTITY and LINEPRICE may vary depending on your database and your setting for Maximum number of rows for test reports in Tools/Options/Run Options.

    

25. Click Close to close Test SQL Statement window, and OK to close Select Statement window.
26. Right-click the workspace to the right of the SQL object and select Add Target, then New.
27. In the erties window, select the adapter you used for your sample procedures, the application directory you are using for your samples, and enter DMXSTORES as the synonym. Click OK.
28. Right-click the DMXSTORES target object, and select Target Transformations.
29. Select STORENAME, PLANT, and PRODNAME as keys by clicking on the key column, and Click OK.
30. Save the data flow as DMXLKUPS.
31. Click the Run button, and select Run from the drop-down menu.