Let Us Learn Oracle - Part 33 of N [ Tree Structure query by using Oracle's Connect By clause ]

This clause creates a report on inheritence tree structure data.

We will look into the TSql Challenge #8 and will solve that by using this clause

SQL> Select LPAD(' ' ,Level * 4) || EMPNAME "Employee Hierarchy"
  2   From tSqlChallenge8 
  3   Start With REPORTSTO Is Null And EMPNAME = 'Jacob'
  4   Connect By Prior EMPID = REPORTSTO;

Employee Hierarchy
--------------------------------------------------------------------------------
    Jacob
        Jess
        Steve
            John
        Bob
            Michael
            Paul

7 rows selected.

The position of PRIOR with respect to the Connect By expression determines which expression identifies the root and which occupies the branches. The LEVEL pseudo-column is an indication of how deep the tree is. Oracle can handle queries with a depth of upto 255 levels. The Start With clause is used to specify the start of the tree. More than one record can match the starting condition.

Instead of hardcoding the Manager name, we can use bind variable and pass the value at runtime

SQL>  Select LPAD(' ' ,Level * 4) || EMPNAME "Employee Hierarchy"
  2    From tSqlChallenge8 
  3    Start With REPORTSTO Is Null And EMPNAME = &EmpName
  4  Connect By Prior EMPID = REPORTSTO;
Enter value for empname: 'Jacob'
old   3:   Start With REPORTSTO Is Null And EMPNAME = &EmpName
new   3:   Start With REPORTSTO Is Null And EMPNAME = 'Jacob'

Employee Hierarchy
--------------------------------------------------------------------------------
    Jacob
        Jess
        Steve
            John
        Bob
            Michael
            Paul

7 rows selected.

We have define a bind variable &EmpName ( any variable name prefixed with ampersand(&) is a bind variable). It will prompt to enter the value at runtime.

For getting a complete report we can do the below

SQL>  Select LPAD(' ' ,Level * 4) || EMPNAME "Employee Hierarchy"
  2    From tSqlChallenge8 
  3    Start With REPORTSTO Is Null
  4  Connect By Prior EMPID = REPORTSTO;

Employee Hierarchy
--------------------------------------------------------------------------------
    Jacob
        Jess
        Steve
            John
        Bob
            Michael
            Paul
    Rui
        Smith
            Lana
            Johnson

        Bobbey
            Mic
            Stev
    Jacobson
        Steffi
            Paulson
        Bracha
            Jessica

19 rows selected.

Hope this is helpful. Thanks for reading