Subquery Returns a Row-Dependent Value
A subquery can return a value that is dependent on the row of the main table. Such a row-dependent subquery is useful when you want to filter rows of the main table based on a comparison with corresponding rows of another table (i.e., the subquery table).
For example, consider a case where one table displays sales by year for NY, and a second table displays sales by year for CA. You want the NY table to display records only for those years where sales were greater than sales in CA. For instance, you want the NY table to display the record for year 2002 only if sales in NY for 2002 were greater than sales in CA for 2002.
To configure a row-dependent subquery, follow these steps:
1. Select the table you wish to filter.
2. Open the 'Condition' dialog box, and then open the 'Subquery' dialog box. See the Filtering and Using a Subquery in a Condition sections for detailed instructions on how to do this.
3. Select the subquery name in the 'Subquery' menu. (In the example above, the subquery is the CA sales table.)
4. Select the desired subquery column from the 'In column' menu. This is the column of the subquery containing the actual values to be tested in the condition. (In the example above, this would be the 'sales' column of the CA table, since this column contains the values that the condition will test.)
5. From the 'Subquery column' menu, select the column in the subquery table that indexes the subquery condition column (selected in the previous step). The 'Subquery column' column is the subquery join column; it will be used to “look up” the condition values corresponding to a particular row in the main table. (In the example above, the 'Subquery column' column would be the 'year' column of the CA table, since this is the column that indexes the 'sales' column of the CA table.)
6. From the 'Current table column' menu, select the column in the main table that contains the indexing values corresponding to those in the 'Subquery column' of the subquery table. (In the example above, the 'Current table column' would be the 'year' column of the NY table, since this is the column that corresponds to the CA 'year' column.)
The 'Subquery column' and 'Current table column' serve to connect the subquery table and main table. For each row in the main table, the value in the 'Current table column' is matched against the values in the 'Subquery column'. The results returned by the subquery for each row are the values of the condition 'In column' in the rows where the 'Subquery column' value is matched.
This example illustrates how to use a row-dependent subquery. Suppose you have the total sales information for all product categories, both for this year and last year. However, you want to display only those product categories that have yielded more sales revenue this year than last year. To do this, you can use last year's total sales as a subquery to provide the data for the condition you place on this year's sales table.

1. Create a new Worksheet.
2. Drag the 'Sales this year' query from the 'Orders' > 'DWS' folder to an empty cell in the Worksheet. This creates a new table named 'Sales this year1'.
3. Drag the 'Sales last year' query from the same folder to an empty cell in the Worksheet. This creates a new table named 'Sales last year1'.
4. Rename the columns in the 'Sales last year1' table: Rename 'Category' to 'Category_LY', and rename 'Total Sales' to 'Total_Sales_LY'. This will make the following steps more clear.

5. Click on the 'Condition' button located in the upper right corner of the 'Sales this year1' table's title bar. This will open the 'Conditions' dialog.
6. Deselect the 'Advanced Conditions' checkbox if it is selected.
7. Click on the 'More' button to open the 'Edit' panel.
8. Using the menus in the 'Edit' panel, specify the following expression:
[Total Sales][is][greater than]

9. Click on the button with the triangle pointing down, located on the far right, and select 'Subquery'. The value text box is replaced with the 'Subquery' button.

10. Click on the 'Subquery' button. This opens the 'Subquery' dialog box.
11. Select 'Sales last year1' from the 'Subquery' menu.
12. Select 'Total_Sales_LY' from the 'In column' menu. This is the column of the subquery containing the values to be used in the condition test.
13. Select 'Category_LY' from the 'Subquery column' column. This is the subquery column that will be matched up against the 'Current table column' in the main table. (It is the “join column” of the subquery table.)
14. Select 'Category' from the 'Current table column' menu. This is the main table column that will be matched up against the 'Subquery column' in the subquery table. (It is the “join column” of the main table.)

15. Click the 'OK' button to close the 'Subquery' dialog box.
16. Click the 'OK' button in the 'Condition' dialog box. The two tables are now shown with a graphical link, indicating that the 'Sales this year1' table relies on subquery data from the 'Sales last year1' table.


17. For each table, press the 'Change View' button in the title bar of the table and select 'Live Preview' from the menu.
18. Expand the table borders as needed to view the results.
19. (Optional) Compare the filtered 'Sales this year1' table to the original 'Sales this year1' table by dragging the 'Sales this year' query (again) from the asset tree to an empty Worksheet cell. Rename it 'Sales this year original'.
Note that the filtered 'Sales this year1' table now contains only the categories for which sales were higher than the previous year.

| << Subquery Returns a Fixed List | © 1996-2013 InetSoft Technology Corporation (v11.5) | 6.3 Grouping >> |