on 05-18-2015 7:41 PM
Hi All,
I am looking for SQL Script code(HANA) to take the input “EMPLoyee table available in the below format and give me the Desired output table .
Input Table EMP_MGR_HOD: All EMPLoyees with their immediate supervisors and HODs
“EMPL”_ID | EMP NAME (All “EMPL”oyees in Org) | IMMEDIATE SUPERVISOR NAME | Dept Head |
101 | JOHN | JACK | Dept head 1 |
102 | ANIL | JACOB | Dept head 1 |
103 | CHRIS | Mike | Dept head 1 |
104 | JACK | JOE | Dept head 1 |
105 | JACOB | JOE | Dept head 1 |
106 | JOE | JIM | Dept head 1 |
107 | JIM | Jolly | Dept head 1 |
108 | JOLLY | Dept head 1 | Dept head 1 |
109 | Dept head 1 | CEO | Dept head 1 |
110 | Dept head 2 | CEO | Dept head 2 |
DESIRED OUTPUT: Traverse Employee Hierarchy from Top to Bottom
Dept Head(L1) | L2 Supervisor | L3 Supervisor | L4 Supervisor | L5..L6… | LOWEST LEVEL (All “EMPL”oyees in Org) | “EMPL”_ID |
Dept head 1 | JOLLY | JIM | JOE | JACK | JOHN | 101 |
Dept head 1 | JOLLY | JIM | JOE | JACOB | ANIL | 102 |
Dept head 1 | Mike | CHRIS | 103 | |||
Dept head 1 | JOLLY | JIM | JOE | JACK | 104 | |
Dept head 1 | JOLLY | JIM | JOE | JACOB | 105 | |
Dept head 1 | JOLLY | JIM | JOE | 106 | ||
Dept head 1 | JOLLY | JIM | 107 | |||
Dept head 1 | Dept head 1 | JOLLY | 108 | |||
Dept head 1 | CEO | Dept head 1 | 109 | |||
Dept head 2 | CEO | Dept head 2 | 110 |
Could you please help me with the SQL Script HANA CODE to achieve the above desired output.
I tried with the following code to trace the EMPLoyee hierarchy from lowest level to higher supervisor levels. But now I want to traverse the EMPLoyee hierarchy levels from TOP (Dept head level) to BOTTOM (EMPLoyee level).
SELECT AE.* ,
EL2.SUPERVISOR_NAME as LVL2_Supervisor,
EL3.SUPERVISOR_NAME as LVL3_Supervisor,
EL4.SUPERVISOR_NAME as LVL4_Supervisor
FROM “EMPL”.EMP_MGR_HOD as AE LEFT OUTER JOIN “EMPL”.EMP_MGR_HOD EL2
ON (AE. Supervisor_id = EL2.”EMPL”ID) LEFT OUTER JOIN “EMPL”.EMP_MGR_HOD EL3
ON (EL2.Supervisor_id = EL3.”EMPL”ID) LEFT OUTER JOIN “EMPL”.EMP_MGR_HOD EL4
ON (EL3.Supervisor_id = EL4.”EMPL”ID);
/* In the above code Employee is L0 , Immediate Supervisor is L1 which are directly above from input table EMP_MGR_HOD table */
The above mentioned script will work for 3 levels (L2,L3,L4) bottom up and we're trying to get all levels between dept head and employee (top down)
Please help us with the SQL Script HANA CODE to achieve the above desired output.
Hi Prabhu,
If you have SAP Data Services in landscape then you can use Hierarchy Flattening transform, or you may check the below link for sql to flatten parent child hierarchy.
Flatten Parent-Child Hierarchy
Regards,
Venkat N.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.