how to debug "ORA-01436: CONNECT BY loop in user data"

I am doing a simple hierarchy tree with parent and child records. Someone entered "bad" data, but out of 400 records I can't find out where/how.

my report query is:
SELECT id, parentid
  FROM table
START WITH id = 1

       CONNECT BY PRIOR id = parentid

the error I get is:
ORA-01436: CONNECT BY loop in user data

I see about 300 records before that throws an error.

I cleaned up a bunch of the data and mysteriously I see about a dozen more items in my result set, but it does NOT appear that any of those new dozen were actually affected by the data clean up.

There is nothing as easy as running a report for all the records where the id and the parentid are equal. I have investigated the data by hand.

Is there a report I can run to identify which records may be causing the problem?

post here.

thanks,

Don Received on Tue Nov 16 2004 - 09:18:35 CST