Using Local Joins as Filters

A filter local join is a variation of a local join. Instead of independently running two queries then locally joining the data on the desktop, a filter local join runs the first query to retrieve a list of values, then uses those values to filter a column in the second query.

For example, a query may be run from an inventory table in an Oracle database to retrieve a list of part numbers that are out of stock. The resulting part number list may be used as a filter join to define the list of values retrieved from a work_in_process table in another database to determine the status of the stock replenishment.

Note:

The second query could potentially be a very long SQL statement since using filter local joins generates an SQL Having clause for each item.

  To use the values retrieved from one query as filter values for another query:

  1. Build the first query to include as a filter in the second query by selecting Insert, then Insert New Query .

    The Insert Query dialog is displayed.

  2. Complete the steps for inserting a new query (described in the Insert Query topic).

  3. Verify item data types and associated data values in source documents so you will know how to join them in the second query.

  4. Build the Request line, and add server filter, data functions and computations to the query as needed.

  5. Select Process.

  7. Select Insert, then Insert New Query.

  8. Build the second query.

    1. Verify item data types and associated data values in source documents so you will know how to join them to the first query.

    2. Build the Request line, and add server and local limits, data functions, and computations to the query as needed.

  9. In the Table catalog of the second query, select Local Results from the shortcut menu.

    A Local Results icon, Local Results icon is displayed in the Catalog frame.

  10. Expand the Local Results icon to display the Results table icon, Results table icon.

  11. Double-click the Results icon or drag it to the Content frame.

    The Results set from the first query that you built is displayed as a topic in the Content pane.

    Note:

    The purpose of embedding the Results is to obtain a list of values. Do not include and Results set topic items on the Request line. Also, do not place any limits on topic items in this Results set. must not include any fields from the embedded Results section. If you do add a topic item from or set a limit on this Results set, you will not be able to set a Limit Local join.

  12. In the Content pane, manually join the Results set to a another topic in the second query.

    A join line is displayed, connecting the different topics.

  13. Double-click the join line that was created by joining the Results set and other topic, or click the Properties icon.

    The Join Properties dialog box is displayed.

  14. Select Filter Local Join and click OK.

    Note:

    If the Filter Local Join option does not display in the Join Properties dialog box, make sure that no Results set topic items are included in the Request line and that no Filter have been placed on any Results set topic item.

  15. Click Process to build the query and apply the filter constraint.