A subquery or subselect can be used in several different places in an SQL SELECT statement.

This example shows how subqueries can be used in the list of columns in a select statement to include values from additional tables without using a JOIN. The product name PRODNAME is retrieved from the inventory table dminv, and the store name STORENAME is retrieved from the table of the company dmcomp.

For the complete example, run the dmsubsel flow that is located in the ibisamp directory (ibisamp/dmsubsel).

1. In the DMC, right-click on an application directory in the navigation pane, select New , and then click Flow.
2. From the ibisamp directory drag dmord as a source to the left of the SQL object.
3. Double-click the SQL object to open the Column Selection dialog box.
4. Ctrl + click STORE_CODE, PROD_NUM, QUANTITY, and LINEPRICE and click the Move Columns Right button to add them to the Selected Columns list.
5. Click the Insert Columns button.

   The SQL Calculator opens.

6. Enter PRODNAME for alias type.
7. Select the Functions tab and expand SQL Operators. Double-click the function that starts with (SELECT lookup_result).
8. Click the ellipsis for LOOKUP_MFD and look in ibsamp for the table dminv. Click Select.
9. Enter T2 for the lookup_mfd_tag type.
10. Click the ellipsis for lookup_result and click PRODNAME.
11. For lookup_aggregation, use the default MIN().
12. Click the ellipsis for lookup_condition. The condition calculator opens. Enter T2.PROD_NUM = T1.PROD_NUM.
13. Click OK.

    The Function Assist dialog box opens, and should resemble the following image.

    

14. Click Ok to close the Function Assist dialog box and click OK to close the SQL Calculator.
15. Repeat steps 5 to 14 using the following values:
    • For LOOKUP_MFD, enter DMCOMP.
    • For lookup_result, enter T3.STORENAME.
    • For lookup_aggregation, enter MIN().
    • For lookup_mfd_tag, enter T3.
    • For lookup_condition, enter T1.STORE_CODE = T3.STORE_CODE.
16. Click OK to close the Column Selection dialog.
17. From the navigation pane in the ibisamp directory, drag the synonym dmstore to the right of the SQL object.
18. Right-click dmstores and click Properties.

    The properties page opens.

19. For the Prior to load option, select Truncate table if available, or Delete all rows if not.

    Click the X to close the page.

20. Double-click dmstores.

    The Transformations window opens.

21. Click the Automap button. Next, click OK to close the Transformations window.
22. On the Flow tab, in the Run group, click Run and select Submit from the drop-down menu.
23. When the flow completes, right-click dmstores, select Operations, and then click sample data. Note that the STORENAME and PRODUCTNAME columns are filled in with values.