In this entry , I will explain how to implement hierarchical queries over ODI via Knowledge Module.
What we have a table which contains employee information and for each employee there is a manager defined.Basic Rule a manager can be an employee which means while A is the manager of B , C is also can be the manager of A etc. Basically in oracle we can use CONNECT BY to show information about hierarchical structures and in this example we will create a KM that can implement a standart CONNECT BY query.
Query as follows:
Our Source table: ( EMPLOYEE)
First of all we gonna create standart mapping as follows:
Filter part contains following sql:
level <= 3 AND department_id = 80
For Execution we choose our new KM called IKM_SQL_Control_Hierarchical
Everthing is almost as same as standart IKM SQL Control Append.We just add specific options for CONNECT BY and START WITH scripts.
IS_NOCYCLE: IF NULL KM use 'CONNECT BY' IF 'YES' KM USE 'CONNECT BY NOCYCLE '
START_WITH_OPTION: IF NULL KM does not use 'START WITH' script Otherwise KM use
the value which is typed into the textbox.
KM use the value which is typed into the textbox.
When Execution completed output look like this:
For more detail you can download the Knowledge Module link below: