Home » SQL & PL/SQL » SQL & PL/SQL » CONNECT BY inner workings (Oracle 10g, WinXP)
CONNECT BY inner workings [message #383743] |
Thu, 29 January 2009 23:02  |
SydneyDotNetter
Messages: 4 Registered: December 2008 Location: Sydney, Australia
|
Junior Member |
|
|
[**PL/SQL SCRIPTS FOR BELOW ATTACHED TO THIS POST**]
Hi,
I have a complex connect by loop which isn't quite behaving as expected, and need to clarify the inner workings of connect by and how it build rows, as well as see if i can do the following using a connect by or not.
What we have is a hierachical self-referencing table, which has an Image_Ref column - which will reference an image table and specify what image an item is associated with. If no image is specified, then 0 is stored as in the schema below:
Ref Parent_Ref Image_Ref
0 0 0
1 0 4
2 1 0
3 2 0
4 3 7
What is needed is to build a hierachical tree, but for each item if it has an Image_Ref of 0, it should inherit the Image_Ref of the next ancestor above it (it should keep going UP until it finds an Image_Ref which is not 0). For the data above, the result SHOULD be:
Ref Parent_Ref Image_Ref
0 0 0
1 0 4
2 1 4
3 2 4
4 3 7
Because Ref 2 has an IMAGE_REF of 0 it should now have an updated IMAGE_REF of 4 (to match it's parent). Then Ref 3 also has an IMAGE_REF of 0, so it should have an updated IMAGE_REF to match it's parent. It's parent is Ref 2 - which should now have an IMAGE_REF of 4 (not it's original value of 0).
The PL/SQL which i have written is as follows. It checks if it's IMAGE_REF is 0, if so, it should take the value of it's parent's IMAGE_REF:
SELECT REF, PARENT_REF,
CASE WHEN PARENT_REF <> 0 AND IMAGE_REF = 0 THEN PRIOR IMAGE_REF ELSE IMAGE_REF END IMAGE_REF
FROM ENTITY
START WITH REF=0
CONNECT BY NOCYCLE PRIOR REF=PARENT_REF
But my assumption here is that CONNECT BY would almost build each row as it goes, then check the value of the row before it (which i've assumed each row can be modified as it goes along).
On a row by row basis, i'm wanting the row with REF of 2 (which has IMAGE_REF of 0) to become an IMAGE_REF of 4 to match it's parent. Then when it moves to it's next child (REF of 3), i want it to get the inherited value of it's parent's IMAGE_REF (which should be 4). However, the CONNECT BY seems to be getting the ACTUAL value from the original table of it's parent, which has an IMAGE_REF of 0. The results come out as:
Ref Parent_Ref Image_Ref
0 0 0
1 0 4
2 1 4
3 2 0
4 3 7
The row with REF 3 should have an IMAGE_REF of 4, but it comes out as 0, because when it evaluates PRIOR IMAGE_REF, it picks up 0. It seems that CONNECT BY doesn't evaluate and build each row as it goes, rather already having the values of parent and children and then comparing against those values.
I would appreciate if anyone could suggest how to get around this (my current solution is to get a cursor, loop through results and insert/update into a temp table,but it's much more involved). Perhaps it isn't possible with the inner workings of CONNECT BY. I hope i have explained this clearly enough, it is fairly difficult to explain accurately.
Many thanks.
[Mod-edit: Frank added [code]-tags to improve readability]
[Updated on: Fri, 30 January 2009 00:48] by Moderator Report message to a moderator
|
|
|
Re: CONNECT BY inner workings [message #383781 is a reply to message #383743] |
Fri, 30 January 2009 02:53   |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I changed parent_ref to NULL for ref=0 to avoid loop, then:
SQL> with
2 first_val as (
3 select ref, max(image_ref) image_ref
4 from ( select ref,
5 first_value(nullif(image_ref,0) ignore nulls)
6 over(partition by ref order by lvl)
7 image_ref
8 from ( select connect_by_root ref ref, level lvl, image_ref
9 from entity
10 connect by prior parent_ref = ref
11 )
12 )
13 group by ref
14 )
15 select e.ref, e.parent_ref, e.image_ref old_image_ref,
16 nvl(f.image_ref,e.image_ref) new_image_ref
17 from entity e, first_val f
18 where f.ref = e.ref
19 order by e.ref
20 /
REF PARENT_REF OLD_IMAGE_REF NEW_IMAGE_REF
---------- ---------- ------------- -------------
0 0 0
1 0 4 4
2 1 0 4
3 2 0 4
4 3 7 7
5 rows selected.
Regards
Michel
|
|
|
|
|
Goto Forum:
Current Time: Wed Feb 12 08:27:48 CST 2025
|