Hierarchical Queries -CONNECT_BY_ROOT Examples

Hierarchical Queries: CONNECT_BY_ROOT Examples The following example returns the last name of each employee in department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id;

Employee        Manager         Pathlen Path
--------------- ------------ ---------- -----------------------------------
Higgins         Kochhar               1 /Kochhar/Higgins
Gietz           Kochhar               2 /Kochhar/Higgins/Gietz
Gietz           Higgins               1 /Higgins/Gietz
Higgins         King                  2 /King/Kochhar/Higgins
Gietz           King                  3 /King/Kochhar/Higgins/Gietz