A correlated subquery is related to a regular subquery in that it uses an inner query to feed result values to the outer query. A correlated subquery executes the outer query multiple times, once for each row returned by the inner query; it is processed by joining a column in the subquery to a column in the parent query.
For example, suppose you had to identify which sales representatives had more sales in the current month than they did in the previous month. The correlated subquery is executed for each row of sales information in the parent query to first determine what were the sales for each representative in the previous month. This data, in turn, is compared to sales for each representative in the current month, and only those representatives whose sales in the current month were greater that their previous month’s sales are returned.
To build a correlated subquery:
Use one of the following options to select an item on which to set a filter:
A subquery indicator shows that you are working with a subquery and not the parent query, even though the parent query’s data models are displayed in the Content pane (which now has a gray background).
The Subquery section is considered a “child” of the parent query and is subordinate to the parent query. That is, it is dependent on the parent query and does not include its own default query or results section.
Subqueries can be nested within other subqueries, in which case, the first subquery becomes the parent query to the subquery nested inside it.
A subquery is correlated based on a join from a column in the subquery to a column in the parent query.
Only one item can be on the Request line, but you can add server and local filters, set a filter inside a subquery as a variable, and define data functions and computations as needed.
If the parent query is not displayed in the Catalog pane, select Show Queries from the shortcut menu in the Section pane.
Drag the parent query into the Content pane.
The parent query is displayed as a blank topic in the Content pane.
Drag the topic to correlate by into the blank parent query topic.
The Select Correlation Column dialog box is displayed and shows the tables of the parent query.
Use the + and – signs to navigate through the structure of the directory tree.
Select the column in the parent to which to join the subquery topic item and click OK.
The topic item is added to the Filter line and a join line is drawn.
Click Process to process the entire query.
The topic item added in the subquery shows the label sub next to the topic item name.