6 Replies Latest reply: Apr 22, 2013 6:16 AM by Karthick_Arp RSS

    Connect by loop in user data

    1003231
      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
          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
            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
              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
                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
                  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
                    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!!