Mappings with SubQuery Filter - ODI 12c

A subquery filter component will help in fetching the data based on subquery result set.
For example if you would like to load Department information data for which department it has less than 10 employees .  We can write a query for this using subquery.

select deptno,dname,loc
from dept
where deptno in (select deptno from emp group by deptno
having count(empno)<10);

1. Create new mapping in ODI task folder and name it( you can uncheck "create empty dataset" checkbox).
2.Add DEPT and EMP tables Logical Tab .
3.Add Subquery Filter component
4.Pull required columns into Subquery Filter component and then to Target table.
5.Do all necessary changes in Subquery filter component.

Overall here it looks.

Note: Mapped EMP.DEPTNO subquery column to DEPT.DEPTNO driving table column under Attributes.
Hence ODI parser will add EMP.DEPTNO subquery column to DEPT.DEPTNO column expression.

And in the Condition ,mentioned below condition

DEPTNO IN
 (SELECT
DEPTNO  
FROM SCOTT.EMP
group by deptno
having count(empno)<10 )

Above expression will be added to WHERE clause of Subquery filter along with operator IN that we mentioned in Subquery Filter Input Role.

Here is the final query generated by ODI.

SELECT
  DEPT_1.DEPTNO  DEPTNO ,
  DEPT_1.DNAME  DNAME ,
  DEPT_1.LOC  LOC
FROM
  SCOTT.DEPT DEPT_1
WHERE
  (
  ( DEPT_1.DEPTNO )
    IN   (
SELECT
  EMP.DEPTNO  DEPTNO
FROM
  SCOTT.EMP EMP
WHERE
  (DEPTNO IN
 (SELECT
DEPTNO  
FROM SCOTT.EMP
group by deptno
having count(empno)<10 )
) )
)