Goodmorning, I've a small problem with my query always get an ORA-01436 CONNECT BY Loop in user data error. I try to create a Tree , I need it in a Report.
OBJ_PARENT (FK required with OBJ_ID)
The Query I try to run is this one.
SELECT LEVEL,LPAD(' ',8*(LEVEL-1))||T_COM_OBJ.OBJ_NAME, T_COM_OBJ.OBJ_PARENT, T_COM_OBJ.OBJ_ID
START WITH T_COM_OBJ.OBJ_ID in (select obj_id obj_main from vdr.t_com_obj where obj_id=obj_parent)
CONNECT BY PRIOR T_COM_OBJ.OBJ_ID = T_COM_OBJ.OBJ_PARENT
I get some result but then he stops and returns me the error.
So if someone has an idee just reply.
1.OBJ_ID and OBJ_PARENT should not have the same value in one record !
2. wrong pairs:
SELECT obj_id, obj_parent from table_a
connect by prior obj_id = obj_parent;
ORA-01436: CONNECT BY loop in user data
[Edited by Ao on 10-02-2002 at 05:59 AM]
Could you explain a bit more what I should change in the Data so my query can run. Are what should I do to create a tree. I'm not very good in these things.
Ao's reply says it all.
Your data is causing a loop. Say we have an employee table with a manager_id that points back to the employee table. Say we try to make a tree query showing who manages who starting with the president. If Bob is the president and Tom works for Bob, we are fine so far, but then if Bob is listed as working for Tom, we have a loop that we cannot get out of. Oracle recognizes this fact and throws an error. Look again at Ao's examples of data that causes a loop. Your data has a loop. Find it and fix it.