Home > SQL, Technical > Hierarchical Query in ORACLE SQL

Hierarchical Query in ORACLE SQL

Hierarchical queries are suitable for tables which is having self foreign key references.

SQL: create table hierarchy(Person_id number primary key, name varchar2(20),ancestor number references hierarchy(person_id));

INSERT SQL:

insert into hierarchy values(1,’Great Grand Father’,”);

insert into hierarchy values(2,’Grand Father’,1);

insert into hierarchy values(3,’Father’,2);
insert into hierarchy values(4,’Son1′,3);
insert into hierarchy values(5,’Son2′,3);
insert into hierarchy values(6,’Son3′,3);
insert into hierarchy values(7,’Grand Son1′,4);
insert into hierarchy values(8,’Grand Son1′,5);
SELECT * from hierarchy;

PERSON_ID NAME ANCESTOR
2 Grand Father 1
3 Father 2
4 Son1 3
5 Son2 3
6 Son3 3
7 Grand Son1 4
8 Grand Son1 5
1 Great Grand Father

Hierarchical Query:-

SELECT LEVEL,PERSON_ID,NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY ANCESTOR=PRIOR PERSON_ID;

LEVEL PERSON_ID NAME
1 1 Great Grand Father
2 2 Grand Father
3 3 Father
4 4 Son1
5 7 Grand Son1
4 5 Son2
5 8 Grand Son1
4 6 Son3

For better understanding, hierarchical query in tree structure

SELECT LEVEL,PERSON_ID,LPAD(‘ ‘,LEVEL*2,’.’) || NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY ANCESTOR=PRIOR PERSON_ID;

LEVEL PERSON_ID LPAD(”,LEVEL..
1 1 . Great Grand Father
2 2 … Grand Father
3 3 ….. Father
4 4 ……. Son1
5 7 ……… Grand Son1
5 9 ……… Grand Son2
5 10 ……… Gra Son3
4 5 ……. Son2
5 8 ……… Grand Son1
4 6 ……. Son3
5 12 ……… grand son2
5 11 ……… grand son1

ORDER SIBLINGS BY:-

SELECT LEVEL,PERSON_ID,LPAD(‘ ‘,LEVEL*2,’.’) || NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID LPAD(”,LEVEL..
1 1 . Great Grand Father
2 2 … Grand Father
3 3 ….. Father
4 4 ……. Son1
5 10 ……… Gra Son3
5 7 ……… Grand Son1
5 9 ……… Grand Son2
4 5 ……. Son2
5 8 ……… Grand Son1
4 6 ……. Son3
5 11 ……… grand son1
5 12 ……… grand son2

SYS_CONNECT_BY_PATH in Hierarchical Query:-

SQL:SELECT LEVEL,PERSON_ID,SYS_CONNECT_BY_PATH(NAME,’/’)
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID SYS_CONNECT_B..
1 1 /Great Grand Father
2 2 /Great Grand Father/Grand Father
3 3 /Great Grand Father/Grand Father/Father
4 4 /Great Grand Father/Grand Father/Father/Son1
5 10 /Great Grand Father/Grand Father/Father/Son1/Gra Son3
5 7 /Great Grand Father/Grand Father/Father/Son1/Grand Son1
5 9 /Great Grand Father/Grand Father/Father/Son1/Grand Son2
4 5 /Great Grand Father/Grand Father/Father/Son2
5 8 /Great Grand Father/Grand Father/Father/Son2/Grand Son1
4 6 /Great Grand Father/Grand Father/Father/Son3
5 11 /Great Grand Father/Grand Father/Father/Son3/grand son1
5 12 /Great Grand Father/Grand Father/Father/Son3/grand son2

CONNECT_BY_ROOT in Hierarchical Query:-

SELECT LEVEL,PERSON_ID,NAME,CONNECT_BY_ROOT NAME AS ANCE
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME ANCE
1 1 Great Grand Father Great Grand Father
2 2 Grand Father Great Grand Father
3 3 Father Great Grand Father
4 4 Son1 Great Grand Father
5 10 Gra Son3 Great Grand Father
5 7 Grand Son1 Great Grand Father
5 9 Grand Son2 Great Grand Father
4 5 Son2 Great Grand Father
5 8 Grand Son1 Great Grand Father
4 6 Son3 Great Grand Father
5 11 grand son1 Great Grand Father
5 12 grand son2 Great Grand Father

SQL:SELECT LEVEL,PERSON_ID,NAME,CONNECT_BY_ROOT NAME AS ANCE
FROM
HIERARCHY
START WITH PERSON_ID=3
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME ANCE
1 3 Father Father
2 4 Son1 Father
3 10 Gra Son3 Father
3 7 Grand Son1 Father
3 9 Grand Son2 Father
2 5 Son2 Father
3 8 Grand Son1 Father
2 6 Son3 Father
3 11 grand son1 Father
3 12 grand son2 Father

Exclude some branches from the tree structure:-

SELECT LEVEL,PERSON_ID,NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
AND PERSON_ID != 5
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME
1 1 Great Grand Father
2 2 Grand Father
3 3 Father
4 4 Son1
5 10 Gra Son3
5 7 Grand Son1
5 9 Grand Son2
4 6 Son3
5 11 grand son1
5 12 grand son2

WHERE CLAUSE IN Hierarchical Query:-

SELECT LEVEL,PERSON_ID,NAME
FROM
HIERARCHY
WHERE PERSON_ID != 5
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME
1 1 Great Grand Father
2 2 Grand Father
3 3 Father
4 4 Son1
5 10 Gra Son3
5 7 Grand Son1
5 9 Grand Son2
5 8 Grand Son1
4 6 Son3
5 11 grand son1
5 12 grand son2

 

Advertisements
Categories: SQL, Technical
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: