ODI - Subquery filter component

A subquery filter component is a projector component that lets us to filter rows based on the results of a subquery. The conditions that we can use to filter rows are Exists, Not Exists, IN and Not In.

For example, the EMP datastore contains employee data and the dept datastore contains dept data. We can use a subquery to fetch a set of records from the Dept datastore and then filter rows from the EMP datastore by using one of the subquery conditions.

Implementation steps:

Drag and drop 2 source datastores and drag and drop subquery filter component and then connect them. Here countries table acts as a driver table and Regions table acts as a subquery filter input table.

Provide subquery filter condition and role as Exist or in or not in etc.

Drag and drop target datastore and connect it with the subquery filter.

Provide LKM and IKM.

We can also use data set as a driver input.

Note: when you use a dataset as driver and if we have same Regions tables name in dataset and outside of it, then it will fail. So change the one of them to have unique names in the logical diagram.