ORA-01436: CONNECT BY loop in user data 2

[appnair]

Can somebody tell me an easy way to understand this query

select .ID,a.OwnerID,a.Type,a.SpaceID,a.Name,a.LeaderID,a.UserPrivileges from KUAF a where a.ID in (select distinct b.ID from KUAFChildren b start with b.ChildID=2753 connect by prior b.ID=b.ChildID)
If I execute this I will get an error ORA-01436: CONNECT BY loop in user data.I know there is a loop somehwhere and would like to fix it.Unfortunately I am not an Oracle expert and the things I know are just about select statments thru TOAD.So please use lay man terms in explaining

Everybody is ignorant, only on different subjects. - Will Rogers

appnair

 

[SantaMufasa]

Appnair,

Oracle's "CONNECT BY...START WITH" syntax accommodates hierarchical "tree walks" within a table. Here is an example:

select lpad(' ',level * 3) || last_name x
  from s_emp
connect by prior id = manager_id
start with manager_id is null;

   Velasquez
      Ngao
         Urguhart
            Maduro
            Smith
         Menchu
            Nozaki
            Patel
         Biri
            Newman
            Markarian
         Catchpole
            Chang
            Patel
         Havel
            Dancs
            Schwartz
      Nagayama
         Magee
         Giljum
         Sedeghi
         Nguyen
         Dumas
      Quick-To-See
      Ropeburn

If you say &quot;connect by prior <PrimaryKey> = <ForeignKey>&quot; (as I did in the query, above), then the tree walk is top down; if you say &quot;connect by prior <ForeignKey> = <PrimaryKey>&quot;, then the tree walk is bottom up.

Your subquery is starting with the row where &quot;b.ChildID=2753&quot;, then gathering all the distinct &quot;b.IDs&quot; that appear in the resulting hierarchy.

The error you are receiving results from a &quot;child&quot; row either directly or indirectly becoming its own parent or grandparent.

To see exactly what is happening in your query, I recommend you try this query:

select lpad(' ',level * 3) || b.ID
from KUAFChildren b
connect by prior b.ID=b.ChildID
start with b.ChildID=2753;

Your query with throw an error when it senses that it is in an &quot;I-am-my-own-grandparent&quot; type loop; By looking at your output, you should be able to see where that data-logic error occurred by detecting where the hierarchy is inappropriately starting over again.

Please advise how this test works for you. If the output is not too much to post here, I'd be very interested in seeing it, as well.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:13 (04Feb04) GMT, 17:13 (03Feb04) Mountain Time)

 

[appnair]

Actaully I did it along the same lines with some trial and error I found my own grand parent.I printed it out as a script using TOAD and it kind of started going thru until it came to the offending entry.Which begs me the question
why does the database allow this type of erron prone tree walking.Isn't there something you guys call as referential integrity and wouldnot it have figured this out?

Everybody is ignorant, only on different subjects. - Will Rogers

appnair

 

[appnair]

Sorry I cannot post the output since I'm glad the vicious cycle has surpassed and I don't want to dwelve too much into it.But this is a good forum and I am getting some good insights in the inner workings now

Everybody is ignorant, only on different subjects. - Will Rogers

appnair