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
table of the company dmcomp.
For the complete example, run
the dmsubsel flow that is located in the ibisamp directory (ibisamp/dmsubsel).
- In the DMC, right-click on an application directory in the navigation
pane, select New , and then click 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 and click the Move
Columns Right button to add them to the Selected Columns
list.
- Click the Insert Columns button.
The
SQL Calculator opens.
- Enter PRODNAME for alias type.
- Select the Functions tab and expand SQL Operators.
Double-click 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 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.
- 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 click 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.
- On the Flow tab, in the Run group, click Run and
select Submit from the drop-down menu.
- 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.