A subquery or subselect can be used in several different
places in an SQL SELECT statement.
Example: Using a subquery in the select list
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.
- In the DMC, right-click on an application directory in the navigation
pane, select New and then select Flow.
- From the ibisamp directory drag dmord as a source to the left
of the SQL object.
- Double-click the SQL object to open the Column Selection dialog
box.
- Ctrl+click STORE_CODE,
PROD_NUM,
QUANTITY,
and LINEPRICE to add them to Selected columns.
- Click the Insert Transforms button.
The
transforms calculator opens.
- Enter PRODNAME for alias type.
- Select the Functions tab and expand SQL Operators. Click on
the function that starts with (SELECT lookup_result).
- Click the ellipsis for LOOKUP_MFD and look in ibsamp for the
table dminv. Click Select.
- Enter T2 for the lookup_mfd_tag type.
- Click the ellipsis for lookup_result and click PRODNAME.
- For lookup_aggregation use the default MIN().
- Click the ellipsis for lookup_condition. The condition calculator
opens. Enter T2,PROD_NUM = T1.PROD_NUM.
- Click Ok.
The Function Assist dialog
box opens and should resemble the following image.
- Click Ok to close the Function Assist
dialog box and click OK to close the SQL
Calculator.
- Repeat steps 5 to 14 using the values shown in the following
image:
- Click OK to close the Column Selection
dialog.
- From the navigation pane in the ibisamp directory, drag the
synonym dmstore to the right of the SQL object.
- Right-click dmstores and select Properties.
The
properties page opens.
- For the Prior to load option, select Truncate table if available,
or Delete all rows if not.
Click the X to
close the page.
- Double-click dmstores.
The Transformations
window opens.
- Click the Automap button. Next, click OK to
close the Transformations window.
- From the Run menu, select Submit.
- 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.