In this entry , I will explain how to implement hierarchical queries over ODI via Knowledge Module.
Case Study:
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)
Target Table: (EMPLOYEE_HR)
Step1:
First of all we gonna create standart mapping as follows:
Filter part contains following sql:
level <= 3 AND department_id = 80
Step2:
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.
KM Options as follows:
OPTIONS:
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.
CONNECT_OPTION:
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:
http://sharesend.com/ynq43t7q
Hi,
YanıtlaSillink http://sharesend.com/ynq43t7q seems to be down, can you send me the IKM mentioned in your post ?
( http://ozukun.blogspot.hr/2014/06/odi-12c-knowledge-module-for.html )
to address kf.ostalo@gmail.com ?
thnx
Hi
YanıtlaSilSorry for late reply , been a while to post this entry.I sent you IKM with e-mail hope it helps.
Hello Ozgur,
Sillike Kresimir, can you send me this ikm in my email: lucasdataintegrator@gmail.com
Thanks!
Hi Lucas
SilI sent your e-mail hope it helps.
Thanks
hy, can you send me the km? thanks.
YanıtlaSilfernandohein@gmail.com
Hi Fernando
YanıtlaSilI sent your e-mail hope it helps.
Thanks
Hi!
YanıtlaSilCan you please send me the km also?
E-mail is perkovicboris@yahoo.com
Thank you!
Hi, Can you please send me KM to my email vinayaseshu@gmail.com?
YanıtlaSilThanks,
Vinay
Hi Vinay
SilSorry for late reply , i sent it.
Hi - great article - exactly what I am trying to do. Any chance you could email me the KM - stephenhrunnalls@gmail.com?
YanıtlaSilThanks
Hi Stephen
YanıtlaSilSorry for late reply , i sent you via e-mail.Hope it helps.
Thanks again.
Hi Yanitla,
YanıtlaSilCOuld you please send the KM on sachchidanandsngh73@gmail.com
Hi
SilSorry for late reply , i sent you via e-mail.Hope it helps.
Thanks again.
Hi Yanıtlar,
YanıtlaSilcould you please send the KM on amurillr@gmail.com?
Thanks in advance.
Hi
SilSorry just read your comment.I send KM tp your email.Hope it helps.
Thanks
can you please email KM to gopal.rk4@gmail.com
YanıtlaSilI send it hope it helps
Silhi
YanıtlaSilplease send me link
hi
YanıtlaSilplease send me link of km
thanks
what is your e-mail?
Silhi
YanıtlaSilcan you please mail the km for hierarchy.(lalith9326@gmail.com)
Hi send it to ur mail
YanıtlaSilHi
YanıtlaSilPlease send the KM to my email, suguselva24@gmail.com
hi,
YanıtlaSilPlease send the KM to my email. ghosh.pritha1993@gmail.com
Hi,
YanıtlaSilCould you please send me the KM? Thanks in advance.
hi, could you send me the KM please? My e-mail adress is ulkudmn2@gmail.com
YanıtlaSilHi . could you please send me the KM? id :kdivyadiya@gmail.com
YanıtlaSilHi send it to ur mail
SilHi,
YanıtlaSilCan you please send me this KM?
ID: ghosh.pritha1993@gmail.com
Hi
YanıtlaSilCan you please send me this KM?
ID: ghosh.pritha1993@gmail.com