Query Problem (ORA-01436 CONNECT BY ....)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query Problem (ORA-01436 CONNECT BY ....)

  1. #1
    Join Date
    Oct 2002
    Posts
    3

    Post

    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.

    Table T_COM_OBJ:

    OBJ_ID (PK)
    OBJ_NAME
    OBJ_DESCRI
    OBJ_COMMENT
    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
    FROM VDR.T_COM_OBJ
    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.

    Thx.

  2. #2
    Join Date
    Jun 2002
    Posts
    22
    1.OBJ_ID and OBJ_PARENT should not have the same value in one record !

    2. wrong pairs:
    OBJ_ID OBJ_PARENT
    1 1
    1 2
    2 1
    SELECT obj_id, obj_parent from table_a
    connect by prior obj_id = obj_parent;
    ERROR:
    ORA-01436: CONNECT BY loop in user data

    [Edited by Ao on 10-02-2002 at 05:59 AM]
    Aleš

  3. #3
    Join Date
    Oct 2002
    Posts
    3
    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.

    Thx.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width