Eliminating Nodes and Branches from a Hierarchical Query : Nodes Branches « Hierarchical Query « Oracle PL / SQL






Eliminating Nodes and Branches from a Hierarchical Query


SQL> CREATE TABLE employee (
  2    employee_id INTEGER,
  3    manager_id INTEGER,
  4    first_name VARCHAR2(10) NOT NULL,
  5    last_name VARCHAR2(10) NOT NULL,
  6    title VARCHAR2(20),
  7    salary NUMBER(6, 0)
  8  );

Table created.

SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 1         ,0            , 'James'  ,'Smith'  ,'CEO',800000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 2         , 1         ,'Ron'     ,'Johnson','Sales Manager',600000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 3         , 2         ,'Fred'    ,'Hobbs'  ,'Sales Person',200000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 4         , 1         ,'Susan'   ,'Jones'  ,'Support Manager',500000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 5         , 2         ,'Rob'     ,'Green'  ,'Sales Person', 40000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 6         , 4         ,'Jane'    ,'Brown'  ,'Support Person',45000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 7         , 4         ,'John'    ,'Grey'   ,'Support Manager',30000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 8         , 7         ,'Jean'    ,'Blue'   ,'Support Person',29000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 9         , 6         ,'Henry'   ,'Heyson' ,'Support Person',30000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 10        , 1         ,'Kevin'   ,'Black'  ,'Ops Manager',100000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 11        , 10        ,'Keith'   ,'Long'   ,'Ops Person',50000);

1 row created.

SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 12        , 10        ,'Frank'   ,'Howard' ,'Ops Person',45000);

1 row created.

SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
  2                 values( 13        , 10        ,'Doreen'  ,'Penn'   ,'Ops Person',47000);

1 row created.

SQL>
SQL>
SQL>
SQL>
SQL> select * from employee;

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME  TITLE                    SALARY
----------- ---------- ---------- ---------- -------------------- ----------
          1          0 James      Smith      CEO                      800000
          2          1 Ron        Johnson    Sales Manager            600000
          3          2 Fred       Hobbs      Sales Person             200000
          4          1 Susan      Jones      Support Manager          500000
          5          2 Rob        Green      Sales Person              40000
          6          4 Jane       Brown      Support Person            45000
          7          4 John       Grey       Support Manager           30000
          8          7 Jean       Blue       Support Person            29000
          9          6 Henry      Heyson     Support Person            30000
         10          1 Kevin      Black      Ops Manager              100000
         11         10 Keith      Long       Ops Person                50000
         12         10 Frank      Howard     Ops Person                45000
         13         10 Doreen     Penn       Ops Person                47000

13 rows selected.

SQL>
SQL> -- Eliminating Nodes and Branches from a Hierarchical Query
SQL>
SQL> SELECT LEVEL,
  2   LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
  3   last_name AS employee
  4  FROM employee
  5  WHERE last_name != 'Johnson'
  6  START WITH employee_id = 1
  7  CONNECT BY PRIOR employee_id = manager_id;

     LEVEL EMPLOYEE
---------- -------------------------
         1  James Smith
         3      Fred Hobbs
         3      Rob Green
         2    Susan Jones
         3      Jane Brown
         4        Henry Heyson
         3      John Grey
         4        Jean Blue
         2    Kevin Black
         3      Keith Long
         3      Frank Howard
         3      Doreen Penn

12 rows selected.

SQL>
SQL>
SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL>

           
       








Related examples in the same category

1.Eliminate an entire branch of nodes from the results of a query, you add an AND clause to your CONNECT BY PRIOR clause