Skip to Main Content
Forums

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Generating Balanced Tree Using SQL Hierarchical Queries

BilalMay 12 2011 — edited May 13 2011
Hi All,

I have the following hierarchical data having different sub-tree levels:

A0
-A001
--A00101
A1
-A101
A2
-A201
--A20101
---A201010001

A0's sub-tree has 3 levels, A1's sub-tree has 2 levels, and A3's sub-tree has 4 level. I want to generate a balanced tree out of the given data having all sub-tree levels equal to the maximum number of levels available in the whole tree which in this particular case is 4.

I dont know it would be possible with SQL. The script to generate the above mentioned data is as below:

CREATE TABLE codes_tree
(node_id VARCHAR2(10),
parent_node_id VARCHAR2(10)
);
INSERT INTO codes_tree VALUES('A0',NULL);
INSERT INTO codes_tree VALUES('A001','A0');
INSERT INTO codes_tree VALUES('A00101','A001');
---
INSERT INTO codes_tree VALUES('A1',NULL);
INSERT INTO codes_tree VALUES('A101','A1');
---
INSERT INTO codes_tree VALUES('A2',NULL);
INSERT INTO codes_tree VALUES('A201','A2');
INSERT INTO codes_tree VALUES('A20101','A201');
INSERT INTO codes_tree VALUES('A201010001','A20101');

Any help will be highly appreciated.

Thanks ... Best Regards

Edited by: naive2Oracle on May 12, 2011 7:40 PM

Edited by: naive2Oracle on May 12, 2011 7:41 PM
This post has been answered by Frank Kulash on May 13 2011
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 10 2011
Added on May 12 2011
9 comments
1,529 views