why the error with looping chain of synonyms...

MIKELALA · #1 (**permalink**) 05-27-10, 15:53

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

Pat Phelan · #2 (**permalink**) 05-27-10, 16:33

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

Internet Services

Web Development

Digital Marketing

Consumer Tech