Use the COUNT() function and LEVEL to get the number of levels in the tree : Level « Hierarchical Query « Oracle PL / SQL

1. Oracle PL / SQL
2. Hierarchical Query
3. Level

Use the COUNT() function and LEVEL to get the number of levels in the tree

 


SQL>
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> --Use the COUNT() function and LEVEL to get the number of levels in the tree
SQL>
SQL> SELECT COUNT(DISTINCT LEVEL)
  2  FROM employee
  3  START WITH employee_id = 1
  4  CONNECT BY PRIOR employee_id = manager_id;

COUNT(DISTINCTLEVEL)
--------------------
                   4

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

Table dropped.

SQL>
SQL>
           
         
  

Related examples in the same category