Help Finding Circular Reference

Hi Foks, I wonder of one of the experts here could help me with a problem...

I have a table, with a parent-child relationship established between some records, using the following fields:

ID_CODE , SERIAL_NO, PARENT_ID_CODE, PARENT_SERIAL. The "PARENT_ xxx" fields on each child record contain the ID_CODE and SERIAL of the parent record in the same table. Each parent can have multiple child records, and each child record can in turn, be a parent to other child records, and so on.

When I run a particular report against this table, the report locks up. The report query contains a Connect By:

CONNECT BY PRIOR PARENT_ID_CODE = ID_CODE AND PRIOR PARENT_SERIAL = SERIAL
START WITH ID_CODE = A AND SERIAL_NO = 1; to ensure the entire chain of records is retrieved starting with the top-level parent record, on down through all the children, sub-children, etc. It appears that the CONNECT BY clause is going into an infinite loop as a result of a circular reference somewhere in the table. Example:

ID_CODE      SERIAL_NO      PARENT_ID_CODE     PARENT_SERIAL
----------        --------------        ---------------------    -----------
----------
A                           1                                 C
3
B                            2                                A
1
C                            3                                B

I've written a simple query which finds any circular reference between the 1st and 2nd level, but I'm having a difficult time coming up with a query which will identify the offenders if there are more than two levels in the relationship. I don't need to correct the report query, I simply need a way to identify any chain of records with a circular reference.

Any help with this would be VERY much appreciated.

If at all possible, a CC of your reply to "dodger at answerdude dot net" (replace at & dot) would put me forever in your debt!

Thanks in advance,

Dodger Received on Mon Nov 03 2003 - 21:15:06 CST