Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2009
    Posts
    56

    Unanswered: why the error with looping chain of synonyms...

    Hello all

    am using sql developer ..am getting the error when i run the given query " looping chain of synonyms"

    SELECT RELATION ,
    FIELD_NAME ,
    ATTRIBUTE ,
    FIELD_DESCRIPTION,
    SOURCE
    FROM VARIABLE_INFO
    WHERE RELATION NOT IN ( "CHAI" ,"ITAB", "ITB2", "ITB3", "MINI", "NOTE", "MINC", "FINI", "FTIM", "FWTG", "FMLY", "FMIS", "FSUM", "MEMB" ,"FINC", "FASO")
    AND FIELD_NAME IN ( SELECT FIELD_NAME FROM VARIABLE_INFO WHERE SUBSTR(FIELD_NAME,-2)
    in ('X1', 'X2' ,'X3', 'X4', 'X5', 'X0', 'XA', 'XB', 'XM' ,'XP' ,'CM','MT')
    OR substr(FIELD_NAME,-1) in ('X') OR substr(FIELD_NAME,1) in ('J') )
    AND ATTRIBUTE LIKE "NUM%" OR ATTRIBUTE = '-' ;

    --> the error is looping chain in synonyms
    line 6 col 5

    I am not able to understand the eror ..

    thanks/mike

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The SQL engine is grumping that it doesn't like VARIABLE_INFO used twice without using a synonym for it. Being a purist, I'd use two different synonyms:
    Code:
    SELECT A.RELATION, A.FIELD_NAME, A.ATTRIBUTE
    ,  A.FIELD_DESCRIPTION, A.SOURCE 
       FROM VARIABLE_INFO AS A
       WHERE A.RELATION NOT IN ('CHAI' ,'ITAB', 'ITB2'
    ,     'ITB3', 'MINI', 'NOTE'
    ,     'MINC', 'FINI', 'FTIM'
    ,     'FWTG', 'FMLY', 'FMIS'
    ,     'FSUM', 'MEMB' ,'FINC'
    ,     'FASO') 
          AND FIELD_NAME IN (SELECT B.FIELD_NAME
             FROM VARIABLE_INFO AS B
             WHERE SubStr(B.FIELD_NAME, -2) in ('X1', 'X2' ,'X3'
    ,           'X4', 'X5', 'X0'
    ,           'XA', 'XB', 'XM' 
    ,           'XP' ,'CM','MT')
                OR SubStr(B.FIELD_NAME, -1) in ('X') 
                OR SubStr(B.FIELD_NAME,  1) in ('J') ) 
          AND A.ATTRIBUTE LIKE 'NUM%' 
          OR  A.ATTRIBUTE = '-' ;
    Note that there are probably more problems to be solved than just this!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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