3

I have a query to see the parent and child hierarchical list of data. On running the below query i got the error ORA-01436.

SELECT ParentPropertyRSN, CONNECT_BY_ROOT PropertyRSN as PropertyRSN,
   LEVEL, SYS_CONNECT_BY_PATH(ParentPropertyRSN, '/') Path
FROM Property
   CONNECT BY PRIOR PropertyRSN = ParentPropertyRSN
   order by level desc;

So I added NOCYCLE in the CONNECT BY clause and got the full list of data with its hierarchical path

Now what I need is a query to get the list of rows that have inaccurate data whih cause ORA-01436.

GMB
  • 216,147
  • 25
  • 84
  • 135
Suwaid Akbar
  • 127
  • 1
  • 8
  • Please post some test data, as text no images, and the expected results. In this case include data that gives the error and some that doesn't (if possible). Further indicate the expected results and indicate those that gives the error. You may also want to review [ask]. – Belayer Feb 27 '20 at 15:12

1 Answers1

7

You should indeed use NOCYCLE to avoid the infinite loop. On top of that, you can use CONNECT_BY_ISCYCLE to identify the offending row(s):

SELECT 
    ParentPropertyRSN, 
    CONNECT_BY_ROOT PropertyRSN as PropertyRSN,
    LEVEL, 
    SYS_CONNECT_BY_PATH(ParentPropertyRSN, '/') Path,
    CONNECT_BY_ISCYCLE Has_Cycle
FROM Property
CONNECT BY NOCYCLE PRIOR PropertyRSN = ParentPropertyRSN
ORDER BY level desc;

From the documentation:

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.

You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

Community
  • 1
  • 1
GMB
  • 216,147
  • 25
  • 84
  • 135