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






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

1.Using the LEVEL Pseudo-Column:display the level in the tree
2.Traversing Upward Through the Tree
3.sort by LEVEL
4.pseudocolumn LEVEL and an example of using the levels with an update.
5.pseudocolumn LEVEL and an example of using the levels.
6.Browse Products with three level nested queries