Oracle SQL - Select Hierarchical Queries

  1. HOME
  2. Oracle SQL
  3. Select
  4. Hierarchical Queries

Introduction

Consider the following query 

select  ename, LEVEL
from    emp
START   WITH mgr is null
CONNECT BY NOCYCLE PRIOR empno = mgr;

The START WITH and CONNECT BY clauses allow you to do the following:

  • Identify a starting point (root) for the tree structure.
  • Specify how you can walk up or down the tree structure from any row.

The START WITH and CONNECT BY clauses must be specified after the WHERE clause and before the GROUP BY clause.

The NOCYCLE keyword in the CONNECT BY clause is optional.

If you omit NOCYCLE, you risk ending up in a loop. If that happens, the Oracle DBMS returns the following error message: 

ORA-01436: CONNECT BY loop in user data

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w w w.j av  a 2s  .c  om

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> select  ename, LEVEL
  2  from    emp
  3  START   WITH mgr is null
  4  CONNECT BY NOCYCLE PRIOR empno = mgr;

ENAME    |     LEVEL
-------- | ---------
KING     |  00001.00
JACK     |  00002.00
SCOTT    |  00003.00
ADAMS    |  00004.00
FORD     |  00003.00
SMITH    |  00004.00
BLAKE    |  00002.00
ALLEN    |  00003.00
WARD     |  00003.00
BROWN    |  00003.00
BREAD    |  00003.00

ENAME    |     LEVEL
-------- | ---------
JONES    |  00003.00
CLARK    |  00002.00
MARY     |  00003.00

14 rows selected.

SQL>

Related Topics

Quiz