Subquery Returns a Fixed List

If you want to use a particular column from the subquery as a fixed list in the condition, you need to specify the query name in the 'Subquery' dialog box, and then select the desired subquery column from the 'In column' menu. A subquery that returns a column is typically used in conjunction with the 'one of' condition clause to filter values that are members (or non-members) of the returned list. You do not need to make selections in the 'Subquery column' and 'Current table column' menus.

Example: Subquery Returns a Fixed List  >>

Assume that you have a table of companies that purchased a 'Wireless Mouse' product, and a second table of companies that purchased a 'Fast Go Game' product. You would like to filter the 'Wireless Mouse' table to show only the companies that did not purchased the 'Fast Go Game.' To do this, define a condition on the 'Wireless Mouse' table that uses the 'Fast Go Game' table as a subquery. Follow the steps below:

1. Create a new Worksheet.

2. Drag the 'Wireless Mouse' query on to an empty cell in the Worksheet. This creates a new table named 'Wireless Mouse1'.

3. Drag the 'Fast Go Game' query on to an empty cell in the Worksheet. This creates a new table named 'Fast Go Game1'.

4. In the 'Fast Go Game1' table, rename the 'Customers' column to 'Customers_FGG'. This will make the next steps more clear.

5. Click on the 'Condition' button located in the upper right corner of the 'Wireless Mouse1' table's title bar. This will open the 'Conditions' dialog box.

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:

[Customers][is not][one of]

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

 

10. Click the 'Subquery' button. This opens the 'Subquery' dialog box.

11. Select 'Fast Go Game1' from the 'Subquery' menu. This is the name of the subquery table to use.

12. Select 'Customers_FGG' from the 'In column' menu. This is the column of the subquery which contains the list of values to use in the condition expression.

 

13. Click 'OK' to close the 'Subquery' dialog box. (You do not need to make selections from the bottom two menus.)

14. Click 'OK' to exit the 'Condition' dialog box.

15. To view the results, press the 'Change View' button in the title bar of each table and select 'Live Preview' from the menu.

 

The 'Wireless Mouse1' table has now been filtered to display only the companies that did not purchase the 'Fast Go Game'. Note that the two tables are now graphically linked together by an arrow to indicate that the 'Fast Go Game1' table provides a subquery result to the 'Wireless Mouse1' table.

The usefulness of defining the filter based on a subquery (rather than hard-coding the list of companies) is that updates to the underlying database which cause the 'Fast Go Game' query to return a different result set will be automatically reflected in the filtered 'Wireless Mouse' table as well.

 

<< Subquery Returns a Fixed Value © 1996-2013 InetSoft Technology Corporation (v11.5) Subquery Returns a Row-Dependent Value >>