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 companies table dmcomp.

For the complete example see ibisamp/dmsubsel.

1. In the DMC, right-click on an application directory in the navigation pane, select New and then select 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 to add them to Selected columns.
5. Click the Insert Transforms button.

   The Transforms Calculator opens.

6. Enter PRODNAME for alias type.
7. Select the Functions tab and expand SQL Operators. Click on 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 values shown in the following image:

    

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 select 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 select sample data. Note that the STORENAME and PRODUCTNAME columns are filled in with values.