Getting CONNECT BY loop in user data:
Table X (sample data actually have 35K rows)
--------------
CODE CASE OLD_ID NEW_ID PERSON AUTH
01 ab122 1234 0001 AU123 99393
07 vv353 7872 0919 FV982 78282
01 ab122 1982 9929 AU123 99393
04 hjsss 8839 8302 JK920 32320
01 ab122 0001 1982 AU123 99393
05 cg899 6728 32322 IKL020 65252
07 w353 0919 8282 FV982 78282
now I need to order this data comparing row values of old_id to new_id for each of the combinations of code, person, case
need output like below
Table X
--------------
CODE CASE OLD_ID NEW_ID PERSON AUTH
01 ab122 1234 0001 AU123 99393
01 ab122 0001 1982 AU123 99393
01 ab122 1982 9929 AU123 99393
04 hjsss 8839 8302 JK920 32320
05 cg899 6728 32322 IKL020 65252
07 vv353 7872 0919 FV982 78282
07 w353 0919 8282 FV982 78282
to get this I am using:
--Query--
select * from table_x
start with old_id not in(
select new_id
from table_x
)
CONNECT BY old_id = PRIOR new_id
AND code = PRIOR code
AND case = PRIOR case
AND person = PRIOR person
--Query--
runs fine with sample data but the problem is when excute it with actual table that has 35K records..
Cause: The condition specified in a CONNECT BY clause caused a loop in the query, where the next record to be selected is a descendent of itself. When this happens, there can be no end to the query.
Action: Check the CONNECT BY clause and remove the circular reference.
Thanks,
AK