0

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
EMPLID  EMP_NAME 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

DeptHead_L1 L2_Sup  L3_Sup  L4_Sup  L5..L6…  LOW_LEVELEMP   “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    Depthead 1                             JOLLY     108
 Dept head 1     CEO                               Depthead 1    109
 Dept head 2     CEO                               Depthead 2    110

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 me with the SQL Script HANA CODE to achieve the above desired output.

gvlasov
  • 18,638
  • 21
  • 74
  • 110
Agile Champs
  • 1
  • It seem that you'll need some logic here to first, look for the deepest level on the hierarchy, and then, create a dynamic SQL query. – Oscar May 18 '15 at 19:11
  • Hi Oscar ..I need help with finding the deepest level and then recursively adding employee id and name of the person reporting to him. – Agile Champs May 18 '15 at 19:33
  • I can tell you what I would do functionally, but can't give you a working example as I don't know the details of HANA – Oscar May 18 '15 at 19:38

0 Answers0