Oracle, SQL and Heirarchical Data Question

[TheExcession]

I have a Table called Groups with a heirarchical relationship,

ID NOT NULL NUMBER(38)
PARENT_GROUP_ID NOT NULL NUMBER(38)
GROUP_NAME NOT NULL VARCHAR2(64)

Now, I can successfully walk down the tree using the 'Connect By Prior' e.g.

select ID, PARENT_GROUP_ID, GROUP_NAME
from BGAN_GROUPS
connect by prior ID = PARENT_GROUP_ID
start with ID = 8;

ID PARENT_GROUP_ID GROUP_NAME
8 0 Test 2 Level 1
9 8 Test 2 Level 2
10 9 Test 2 Level 3a
11 9 Test 2 Level 3b


But I need to supply a leaf node ID and walk back up the tree until I hit the root.

I thought I could just change "connect by prior ID = PARENT_GROUP_ID" to
"connect by prior PARENT_GROUP_ID = ID", but of course this breaks as essentially it is turning the tree upside down and fails when you hit a level that has two child nodes.

Anyone know how to write some SQL to walk up the tree, towards the root given a leaf node ID rather than a Root Node ID?

many thanks
Ex

[3200ac]

if you're up to speed with PL/SQL you'll be able to load a collection and manipulate the tree from there

[TheExcession]

3200ac said:

if you're up to speed with PL/SQL you'll be able to load a collection and manipulate the tree from there

Here in lies the problem, my experience level with PL/SQL is about 1 day, I can manage selects and inserts, but Unions, Inner Joins, Outter Joins etc leave me in a cold sweat...

Hence the cry for help....

How, given an ID, do I traverse up the tree related to by Parent_Group_ID intil I hit ID = 0 - the root node?

I'm sure it's pretty simple, but so far an hour of googling has left me non the wiser.

cheers
Ex

[3200ac]

what's your timescale? I should get a slot by this evening to send working example...

[TheExcession]

3200ac said:

what's your timescale? I should get a slot by this evening to send working example...

about three days ago

If someone else doesn't show up I'll certainly take you up on it!

many thanks
Ex

[3200ac]

ok here's a version with just sql, I'm not sure if it's the exact solution, but shows the path of IDs from the top of the tree down to the given leaf node (comments should help):-

----------------------------------------------------------------------------
SELECT ParentIDs
FROM
(-- connect by clause used within the in-line view here...
SELECT TRIM(LEADING ',' FROM SYS_CONNECT_BY_PATH(b.ID, ',')) ParentIDs,
b.id ID
FROM bgan_groups b
connect by prior b.ID = b.PARENT_GROUP_ID
start with b.id = 1 -- always go from top of the tree...
)
where ID = 7 -- this predicate identifies the leafnode we display the path to...
----------------------------------------------------------------------------

Here's script to create table/data I used...

create table bgan_groups
(
ID NUMBER(*,0) NOT NULL,
PARENT_GROUP_ID NUMBER(*,0) NOT NULL,
GROUP_NAME VARCHAR2(64) NOT NULL);

-- L0
insert into bgan_groups
values (1,0,'1st Group (L0)');

-- L1
insert into bgan_groups
values (2,1,'2nd Group (L1)');

insert into bgan_groups
values (3,1,'3rd Group (L1)');

insert into bgan_groups
values (4,1,'4th Group (L1)');

-- L2
insert into bgan_groups
values (5,3,'5th Group (L2)');

insert into bgan_groups
values (6,4,'6th Group (L2)');

-- L3
insert into bgan_groups
values (7,5,'7th Group (L3)');

COMMIT;

And the rsult is:- 1,3,5,7

Combining the in-line view and SYS_CONNECT_BY_PATH does the business, without needing further collection manipulation in this case, but depends on if this is your requirement - let me know?
Ta,
M



>> Edited by 3200ac on Monday 5th December 13:48

[TheExcession]

Hi MArk,

thanks for taking the time to look into this, I'm still getting:
ORA-01436: CONNECT BY loop in user data.

I think the problem is that this method turns the tree upside down. Then if you hit two nodes at one level it doesn't know which to follow.

I guess I should've posted my test data before.

ID PARENT_GROUP_ID GROUP_NAME
0 0 Default Group
2 0 BGAN Unit Testing - Group 2
3 0 BGAN Unit Testing - GRoup 3
4 0 Test 1 Level 1
5 4 Test 1 Level 2
6 5 Test 1 Level 3a
7 5 Test 1 Level 3b
8 0 Test 2 Level 1
9 8 Test 2 Level 2
10 9 Test 2 Level 3a
11 9 Test 2 Level 3b


So essentially -
if I supply ID 11, I'd like to get records 9,8 & 0
if I supply ID 10, I'd like to get records 9,8 & 0
if I supply ID 9, I'd like to get records 8 & 0
if I supply ID 3, I'd like to just get record 0

in all cases it doesn't matter if I actually get the record ID'd so
if I supply ID 11, then records 11,9,8 & 0 is fine.. like wise
if I supply ID 10, then records 10,9,8 & 0
if I supply ID 3, then records 3 & 0

If that makes sense.....
Just a case of walking up from the supplied ID, picking the next parent record until ID = 0 (or infact, until ID=Parent_Group_ID = that way I can have many root nodes in the table, roots being identifiable by the fact they poin to themselves).

cheers Mate!

[3200ac]

okey dokes, I think it'll be worth getting the supplied sql working in your environment/data, remove all the comments and make sure the predicate values are appropriate (there's no ID = 1, which may explain why it's producing the error you're seeing). If you need to break out the in-line view into a stand-alone piece of sql and get that working, then put the working sql back into the in-line view to filter to the required row...
The sys_connect_by_path is worth persevering with as it's built into the Oracle kernel, so super-fast compared to anything I could write with collection manipulation (even using BULK COLLECT to speed it up)
I'll try with your data later. Traditionally the root node is identified by having a parent of null, is it an option to amend your table/logic to use this representation? This may also be the cause of error...
M

>> Edited by 3200ac on Monday 5th December 14:20

[boiler]

Excession, I think you were on the right track - what error messages were you getting?

Could it be because as you walk back up the tree, your root node has got a 0 in the parent ID, which cannot be found?

Andy.

just read your test data - the loop is because your root node points to itself. If you need to keep the constraint on the parent id column, could you use -1 or something else to flag it as the root node? That of course depends on what other functionality is relying on that piece of knowledge.

>> Edited by boiler on Monday 5th December 14:45

[TheExcession]

^^^ wot he said.

Boiler seems bang on the money here, once I changed my root nodes to have a parent_id of -1 the original SQL worked fine.

So seems you can walk both ways through the tree like I originally tried with the

connect by prior ID = PARENT_GROUP_ID -- down the tree

and

connect by PARENT_GROUP_ID = prior ID -- up the tree


Many many thanks to those posting their comments here.

best
Ex

[TheExcession]

oh... I forgot to mention, like HTML, I fecking hate SQL

[boiler]

[quote=TheExcession
connect by PARENT_GROUP_ID = prior ID -- up the tree
[/quote]

i think you meant

connect by prior PARENT_GROUP_ID = ID

Andy.

[3200ac]

have got the collection based version going, with a bit of recursion just for good measure, although the version you've got does the biz, so all good...