Hirarchical_support01

View All Public Scripts Login to Run Script

Script Name Hirarchical_support01
Visibility Unlisted - anyone with the share link can access
Description Hierarchical SQL support
Area SQL General
Contributor sagardua297@gmail.com
Created Wednesday February 03, 2021

Statement 1

CREATE TABLE ENTITIES(PARENT_ENTITY VARCHAR2(20 BYTE),CHILD_ENTITY VARCHAR2(20 BYTE),VAL Number)

Table created.

Statement 2

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values (NULL,'a',100)

1 row(s) inserted.

Statement 3

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('a', 'af',50)

1 row(s) inserted.

Statement 4

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('a', 'ab',50)

1 row(s) inserted.

Statement 5

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('a', 'ax',50)

1 row(s) inserted.

Statement 6

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('ab', 'abc',10)

1 row(s) inserted.

Statement 7

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('ab', 'abd',10)

1 row(s) inserted.

Statement 8

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('ab', 'abe',10)

1 row(s) inserted.

Statement 9

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('abe', 'abes',1)

1 row(s) inserted.

Statement 10

 Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('abe', 'abet',1)

1 row(s) inserted.

Statement 11

 insert into entities values('c','ab',800)

1 row(s) inserted.

Statement 12

insert into entities values('d','abe',700)

1 row(s) inserted.

Statement 13

 Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values (NULL,'b',100)

1 row(s) inserted.

Statement 14

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('b', 'bg',50)

1 row(s) inserted.

Statement 15

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('b', 'bh',50)

1 row(s) inserted.

Statement 16

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('b', 'bi',50)

1 row(s) inserted.

Statement 17

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('bi', 'biq',10)

1 row(s) inserted.

Statement 18

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('bi', 'biv',10)

1 row(s) inserted.

Statement 19

 Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY,VAL) Values ('b', 'abet',19)

1 row(s) inserted.

Statement 20
```
COMMIT
```
```
Statement processed.
```

Statement 21

SELECT child_entity AS child, parent_entity AS Parent, level, CONNECT_BY_ISCYCLE AS IsLoop, SYS_CONNECT_BY_PATH(child_entity,'\') AS Path 
FROM entities 
START WITH child_entity = 'abet' 
CONNECT BY NOCYCLE child_entity = PRIOR parent_entity 
ORDER SIBLINGS BY child_entity


CHILD PARENT LEVEL ISLOOP PATH
abet abe 1 0 \abet
abe ab 2 0 \abet\abe
ab a 3 0 \abet\abe\ab
a  - 4 0 \abet\abe\ab\a
ab c 3 0 \abet\abe\ab
abe d 2 0 \abet\abe
abet b 1 0 \abet
b  - 2 0 \abet\b

8 rows selected.

CHILD	PARENT	LEVEL	PATH
abet	abe	1	\abet
abe	ab	2	\abet\abe
ab	a	3	\abet\abe\ab
a	-	4	\abet\abe\ab\a
ab	c	3	\abet\abe\ab
abe	d	2	\abet\abe
abet	b	1	\abet
b	-	2	\abet\b

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database