This discussion is archived
6 Replies Latest reply: Apr 22, 2013 4:16 AM by Karthick_Arp RSS

Connect by loop in user data

1003231 Newbie
Currently Being Moderated
Dear all,


could anyone please let me know the reason that why i'm getting error ''*Connect by loop in user data*'' for the following query

SELECT distinct AAGTNO,GSM_CODE,AM_CODE
from ext_lsp_ptaagr09
START WITH AAGTNO = '13857'
CONNECT BY PRIOR AAGTNO = gsm_code

i am using 11g database.

Thanks
  • 1. Re: Connect by loop in user data
    jeneesh Guru
    Currently Being Moderated
    You could try NOCYCLE..
    CONNECT BY nocycle PRIOR AAGTNO = gsm_code
    But that may not help you.. Better you explain your requirement..Provide sample data and expected output.... People will be able to help you..
  • 2. Re: Connect by loop in user data
    Karthick_Arp Guru
    Currently Being Moderated
    Here is a simple example
    SQL> create table t
      2  (
      3    child_id integer, 
      4    parent_id integer
      5  );
     
    Table created.
     
    SQL> insert into t values (1, 2);
     
    1 row created.
     
    SQL> insert into t values (2, 1);
     
    1 row created.
     
    SQL> select *
      2    from t
      3  connect by child_id = prior parent_id;
    ERROR:
    ORA-01436: CONNECT BY loop in user data
     
     
     
    no rows selected
    To overcome this you can use NOCYCLE
     
    SQL> select *
      2    from t
      3  connect by nocycle child_id = prior parent_id;
     
      CHILD_ID  PARENT_ID
    ---------- ----------
             1          2
             2          1
             2          1
             1          2
     
    SQL> 
  • 3. Re: Connect by loop in user data
    1003231 Newbie
    Currently Being Moderated
    Actually my requirement is
    i have one table called ptr09 with three columns agntcd, gsmcd, amcd, companycd
    amcd is parent to gsmcd and also one agent .
    gsm is parent to agntcd and gsm also one agent.
    agntcd also one agent.
    then i'll pass one agent code as input and dont know whether it is agntcd or gsmcd or amcd
    Now i have to return data for input agent.
    for example i gave amd then expected output will be amcd, related gsmcd and related agntcd
    if input is gsm then expected output is gsmcd and agntcd
    input agnt means expected output is agntcd only.

    please assist me on this issue...
    thank you.
  • 4. Re: Connect by loop in user data
    Karthick_Arp Guru
    Currently Being Moderated
    1000228 wrote:
    Actually my requirement is
    i have one table called ptr09 with three columns agntcd, gsmcd, amcd, companycd
    amcd is parent to gsmcd and also one agent .
    gsm is parent to agntcd and gsm also one agent.
    agntcd also one agent.
    then i'll pass one agent code as input and dont know whether it is agntcd or gsmcd or amcd
    Now i have to return data for input agent.
    for example i gave amd then expected output will be amcd, related gsmcd and related agntcd
    if input is gsm then expected output is gsmcd and agntcd
    input agnt means expected output is agntcd only.

    please assist me on this issue...
    thank you.
    Rather than just words some sample data would help. Please read {message:id=9360002}
  • 5. Re: Connect by loop in user data
    1003231 Newbie
    Currently Being Moderated
    actually iam doing like this...

    SELECT DISTINCT AAGTNO,GSM_CODE,AM_CODE,com.m1agno,

    fsr.policy_number as PolicyNo,

    fsr.source_system_name

    from channel_role cr,

    ptaagr09 pta ,

    comagtm1 com,

    financial_services fs



    WHERE com.m1agno = '26745' and

    com.M1AGNO = cr.agent_code

    AND pta.aagtno = cr.agent_code

    and cr.role_player_id = fs.role_player_id

    and fs.source_system_name = 'LA'

    and fs.Type_Description IN ('AG','AGT')

    and pta.ACO in ('IL', 'HS','AL')

    START WITH pta.aagtno = '26745'

    CONNECT BY PRIOR pta.gsm_code = pta.am_code

    is this will work????

    expected output will be like this

    AAGTNO GSM_CODE AM_CODE
    00247     00247     04224
    00289     06517     06519
    00374     05934     09000
    13283     40772     41418
    14880     14880     41418
    16418     01100     05304
    17903     03036     01122
    18075     02161     09731
    19317     01484     07131
    11925     11925     09005
    13562     02234     00684
    13606     10310     09764
    16715     07189     04224
    16847     08428     07566




    AAGTNO is child to GSM_CODE and GSM_CODE is child to AM_CODE
    i have to get all the related data when i give input such as AAGTNO , GSM_CODE , AM_CODE

    Edited by: 1000228 on Apr 22, 2013 4:08 AM
  • 6. Re: Connect by loop in user data
    Karthick_Arp Guru
    Currently Being Moderated
    1000228 wrote:
    actually iam doing like this...
     
    SELECT DISTINCT AAGTNO,GSM_CODE,AM_CODE,com.m1agno, 
    fsr.policy_number as PolicyNo, 
    fsr.source_system_name ;                         
    from   channel_role cr, 
    ptaagr09 pta ,                        
    comagtm1 com,                  
    financial_services fs     
    WHERE     com.m1agno ;               = '26745' and 
    com.M1AGNO ;             = cr.agent_code ;  
    AND pta.aagtno ;             = cr.agent_code 
    and cr.role_player_id ;      = fs.role_player_id ;   
    and fs.source_system_name ; = 'LA' 
    and fs.Type_Description IN ('AG','AGT') 
    and pta.ACO in ('IL', 'HS','AL') 
    START WITH pta.aagtno = '26745' 
    CONNECT BY PRIOR  pta.gsm_code = pta.am_code 
    is this will work????
    How would we know?

    We don't have access to your DB and you have decided not to provide any sample data or table structure. And more of all you have completely neglected to read the FAQ link posted previously. So now how will we know?
    expected output will be like this
     
    AAGTNO   GSM_CODE AM_CODE 
    00247 00247 04224 
    00289 06517 06519 
    00374 05934 09000 
    13283 40772 41418 
    14880 14880 41418 
    16418 01100 05304 
    17903 03036 01122 
    18075 02161 09731 
    19317 01484 07131 
    11925 11925 09005 
    13562 02234 00684 
    13606 10310 09764 
    16715 07189 04224 
    16847 08428 07566 
    Without knowing the input, output is of no use!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points