Oracle Programming/Retrieving Data Using Sub-queries

Hierarchical Query

Hierarchical Query allows you the transverse through a self-reference table and display the Hierarchical structure. eg. the employee table contain the manager id the employee.

list out the whole hierarchical structure of the employees

SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", salary, department_id
FROM hr.employees
CONNECT BY PRIOR employee_id = manager_id
      START WITH manager_id IS NULL
ORDER SIBLINGS BY last_name;

list out all the employees under manager 'Kochhar'

SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", 
       salary, 
       department_id,
       CONNECT_BY_ISLEAF
FROM hr.employees
  CONNECT BY PRIOR employee_id = manager_id
        START WITH last_name = 'Kochhar'
 ORDER SIBLINGS BY last_name;

list out all the manager that 'Lorentz' report to

SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", salary, department_id,
       SYS_CONNECT_BY_PATH(last_name, '/') "Path", CONNECT_BY_ISLEAF
FROM hr.employees
        CONNECT BY employee_id = PRIOR manager_id
        START WITH last_name = 'Lorentz'
 ORDER SIBLINGS BY last_name;

• pseudocolumn LEVEL -> root = 1, next level=2,3,4,5...etc
• SYS_CONNECT_BY_PATH(col, '/') shows the full path, 2nd parameter is seperator (9i)
• CONNECT_BY_ROOT(col) return the value of the root node in the current hierarchy (10g)
• pseudocolumn CONNECT_BY_ISLEAF return 1 if the return value is at the last node on the Hierarchy (ie. leaf) (10g)
• order SIBLINGS by re-order the sequence of the output and preserve the hierarchical relationship (10g)

• connect by NOCYCLE prior child = parent
  • NOCYCLE means stop tranverse the hierarchy at the level when the child reference back to the root. (10g)
  • pseudocolumn CONNECT_BY_ISCYCLE evaluate to "1" if the current row references a parent. (10g)