Adding Subqueries

Problem

You want to add one or more subqueries to an existing SQL query by using the Designer tab of the properties window for the SQL Join transformation.

Solution

Use the Subquery object in the Designer window to add a subquery to an SQL query. The sample job used in Add a Subquery As an Input Table adds a subquery to an input table. This subquery reduces the amount of data that is processed in the main SQL query because it runs and subsets data before the SELECT clause is run. Add a Subquery to an SQL Clause covers adding a subquery to a SELECT, WHERE, or HAVING clause in an SQL query.

Perform the following tasks:

Tasks

Add a Subquery As an Input Table

You can add the source and target tables directly to the process flow diagram for the job. You can also add a table, join, or subquery to a job by dragging and dropping it on the Diagram tab in the Designer window for the SQL Join transformation. If you drop a table on an existing table in the Designer tab, the new table replaces the existing table.

You can even add a new input port to the query flow on the Diagram tab. To perform this task, select one of the join icons from the Joins directory in the SQL Clauses pane and drop it on the Diagram tab. The join and its input port is displayed in the query flow in the tab, where you can connect it to the appropriate parts of the SQL query. Use this method to add a subquery to the job.

Perform the following steps to create a subquery that refines the SQL query:

Select the SubQuery object in the Select Clauses folder in the SQL Clauses pane, and drop it in a blank space in the Diagram tab.
Select the Inner join object in the Joins folder in the SQL Clauses pane, and drop it in a blank space in the Diagram tab.
Disconnect the existing join from the Select object. Click on the arrow between the Join and the Select object. Then, press DELETE to remove the arrow. The subquery, the inner join, and the original join are displayed in the query flow, as shown in the following display.

Initial Subquery on Inner Join
Move the subquery and the new join to appropriate locations. Then, complete the following actions:
- Connect the subquery to an input port of the new join.
- Connect the original join to the remaining input port of the new join.
- Connect the new join to the input port of the Select object.
A sample subquery on an inner join is shown in the following display.

Connected Subquery On Inner Join
Click the SubQuery object. Note that the SubQuery Properties pane displays. Enter an appropriate value in the Alias field. (RegionQry was entered in the sample job.) If you do not enter an alias here, then the subquery fails. The system-generated name for the subquery results table is too ambiguous to be recognized as an input to the full SQL query.
Click SubQuery in Navigate pane. The Select object for the Subquery is displayed on a Diagram tab.
Drop the source table on the Diagram tab. The source table for the sample job is named Region.
Double-click Select to display the Select tab. Make sure that the source table columns are mapped properly to the target table. Also, ensure that the Select * property in the Select Properties pane is set to No.
Click SubQuery in the Navigate pane to return to the SubQuery tab. Then, select Where in the SQL Clauses folder of the SQL Clause pane. Finally, drop the Where icon into an empty spot in the Diagram tab. A Where clause object is added to the Diagram tab. The completed subquery flow is shown in the following display.

Sample Subquery Flow
Double-click Where to display the Where tab.
Click New on the Where tab to begin the first part of the expression. An editable row appears in the table near the top of the tab.
Create your first WHERE condition. In this example, a subset of the Region column from the Region table to select values from the eastern region was created. To recreate the condition, click the drop-down menu in the Operand field on the left side of the row, and click Choose column(s). Then, drill down into the Region table, and select the Region column. The value r.Region displays in the field.
Keep the defaulted value of = in the Operator field. Enter the value 'E' in the Operand field on the right side of the row.
Create the remaining conditions for the WHERE statement. Review the SQL code that is generated in this step in the SQL field, as shown in the following display.

Where Tab in the Subquery
A connection is required between the source table for the subquery and the target table for the full SQL query. To recreate the sample, right-click in the Target table field of the Select tab and click New Column in the pop-up menu.
Enter name of the subquery source table in the Name field. Then, make sure that the new column has the appropriate data type. In this case, the Region table is added to the target table in the SQL query.
Add a mapping for the subquery to the main query SELECT clause. In the sample query, the Region column from the Region table in the subquery is mapped to the Region column in the target table. Also, the following condition is added to the main query WHERE clause:
```
and RegionQry.Region = Region
```
This condition connects the inner join subquery to the main query.

Note: You can add a subquery to any place that you can add a table.

Add a Subquery to an SQL Clause

You can also add a subquery to SELECT, WHERE, HAVING clauses in SQL queries. The following display shows how a subquery can be added as a condition to a WHERE clause.

Add a Subquery to a WHERE Clause

Note that the subquery is connected to the WHERE clause with the EXISTS operator, which you can select from the drop-down menu in the Operator field. To add the subquery, click in the Operand field on the right-hand side of the Where tab. Then, click Subquery from the drop-down menu. The following display shows the completed sample subquery.

Sample WHERE Clause Subquery

The subquery includes a source table, a SELECT clause, and a WHERE clause. You can compare the tree view of the subquery in the Navigate pane to the process flow on the Diagram tab and the code that is highlighted on the Code tab.