Friday, 20 February 2015

Query to build the  list the supervisors for a given employee in a single column

the format is as

Employee_1 -> Employee_2 -> Employee_3 -> Employee_4
where Employee_2 is the supervisor of Employee_1 and so on.




SELECT
 DISTINCT SYS_CONNECT_BY_PATH(FULL_NAME, '->') "Path",
LEVEL LVL
FROM
  PER_ALL_PEOPLE_F PPFS,PER_ALL_ASSIGNMENTS_F PAFE
WHERE 1 = 1
and PPFS.PERSON_ID =  PAFE.person_ID
AND TRUNC (SYSDATE) BETWEEN PAFE.EFFECTIVE_START_DATE AND PAFE.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPFS.EFFECTIVE_START_DATE AND PPFS.EFFECTIVE_END_DATE
and level = 3
  CONNECT BY Ppfs.PERSON_ID = PRIOR Pafe.SUPERVISOR_ID  -- PPFS,PAFE
 START WITH Ppfs.PERSON_ID = &Numbe ;

No comments:

Post a Comment