Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can Sequencing done using Hierarchical query if data arrange in parent-child relationship.

sanju_sql_plsqlJun 9 2016 — edited Jul 11 2016

I have below table with data arrange in parent-child relationship


CREATE TABLE tab1
(
  id        NUMBER,
  parent_id NUMBER

);

INSERT INTO tab1 VALUES (1, NULL);
INSERT INTO tab1 VALUES (2, 1);
INSERT INTO tab1 VALUES (3, 2);
INSERT INTO tab1 VALUES (4, 2);
INSERT INTO tab1 VALUES (5, 4);
INSERT INTO tab1 VALUES (6, 4);
INSERT INTO tab1 VALUES (7, 1);
INSERT INTO tab1 VALUES (8, 7);
INSERT INTO tab1 VALUES (9, 1);
INSERT INTO tab1 VALUES (10, 9);
INSERT INTO tab1 VALUES (11, 10);
INSERT INTO tab1 VALUES (12, 9);

INSERT INTO tab1 VALUES (24, null);
INSERT INTO tab1 VALUES (21, 24);
INSERT INTO tab1 VALUES (25, 21);
INSERT INTO tab1 VALUES (22, 21);

INSERT INTO tab1 VALUES (26, null);
INSERT INTO tab1 VALUES (22, 26);
INSERT INTO tab1 VALUES (22, 21);
INSERT INTO tab1 VALUES (27, 22);

INSERT INTO tab1 VALUES (28, null);
INSERT INTO tab1 VALUES (23, 28);
INSERT INTO tab1 VALUES (29, 23);

Can I achieve run sequence of ids as below

TREE       Run_Sequence
---------- ------------
1          1     
..2        1.1   
....3      1.1.1 
....4      1.1.2
......5    1.1.2.1     
......6    1.1.2.2
..7        1.2
....8      1.2.1
..9        1.3
....10     1.3.1     
......11   1.3.1.1
....12     1.3.2

24    2
..21    2.1
....25    2.1.1
....22     2.1.2
......27   2.1.2.1

26    3
..22    3.1
....27     3.1.1

28    4
..23    4.1
....29     4.1.1

Thanking in advance

Sanjeev

This post has been answered by Frank Kulash on Jun 9 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 8 2016
Added on Jun 9 2016
32 comments
4,529 views