I have a query in Teradata where I am passing a variable from SQL server through a process.
SELECT * FROM MyView WHERE INDEX( Group_ID, @GroupID ) > 0
It transforms to
SELECT * FROM MyView WHERE INDEX( Group_ID, ''2565','5216','5364'' ) > 0
It throws error
 Syntax error, expected something like ',' between a string or a Unicode character literal and the integer '2565'
If only one value is passed, for example '2565' then query works fine. I am not sure what am I missing. OR if I should use some other function besides INDEX. Basically, in simple words what I am trying to achieve is:
SELECT * FROM MyView WHERE Group_ID in (@GroupID)
What is the data type of Group_ID?
From the Documentation:
Returns the position in string_expression_1 where string_expression_2 starts.
( string_expression_1 , string_expression_2 )
So you might need to exchange the order to
' is opening and closing a String.
So '' is a empty sting. Therefor 2565 is interpreted as Integer...
if '' is within a String it is interpreted as '
Did you consider to write your query with an IN clause?
'''2565'',''5216'',''5364''' did not work. Data type of Group_ID is int.
IN clause was the first thing I tried. It works if I pass hard coded values, but if I pass values as variable @Group it did not work. I guess it is adding extra quotes around it. What I mean is if I try
WHERE Group_ID IN ('2445','1452'), it works, but when I pass @Group, WHERE Group_ID in (@Group), it converts to WHERE Group_ID IN (''2445','1452'') adding extra quotes and does not return anything. Can you suggest any solution to this? Or if you can suggest any other function which might work in this situation.
maybe like will be easier...
remove all internal ' from the @Group_ID list and try
where ','!!@Group_ID!!',' like ' %, ' !! trim(Group_ID) !! ', %' "
you can check how this works with
select * from sys_calendar.calendar where ',1,11,111,3212,' like '%,' !! trim(day_of_calendar) !! ',%'
I guess this is another thread parallel to http://community.teradata.com/t5/Database/TRIM-BOTH-Teradata-not-working-for-single-quotes/m-p/73288....
I don't think we can be any more specific until we understand what "I am passing a variable from SQL server through a process" means. What is the process? What doe it mean that it is from SQL Server? Are you actually executing this in SQL Server? If so, then you have to edit @GroupID to remove the quotes as discussed previously, and construct the SQL statement as a string to be dynamically executed.