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 Go to next message
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 Go to previous messageGo to next message
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
Re: CONNECT BY inner workings [message #383806 is a reply to message #383781] Fri, 30 January 2009 05:59 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
/forum/fa/450/0/ Nice one, Michel! I was trying something similar but this is better than what I had in mind.

MHE
Re: CONNECT BY inner workings [message #384015 is a reply to message #383806] Sun, 01 February 2009 18:41 Go to previous message
SydneyDotNetter
Messages: 4
Registered: December 2008
Location: Sydney, Australia
Junior Member
Hi Michel,

This has worked very nicely, thanks indeed for your help here it's much appreciated Cool

Regards,

Tony.
Previous Topic: Is my assumption correct in the correlated update query
Next Topic: Order By Clause
Goto Forum:
  


Current Time: Wed Feb 12 08:27:48 CST 2025