INDEX function

Database
Sir
Enthusiast

INDEX function

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

[3707] 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)

Please guide.

5 REPLIES
Supporter

Re: INDEX function

What is the data type of Group_ID?

 

From the Documentation:

INDEX
Purpose
Returns the position in string_expression_1 where string_expression_2 starts.
Syntax
INDEX
( string_expression_1 , string_expression_2 )

 

So you might need to exchange the order to

@Group_ID, Group_ID

 

''2565','5216','5364''

'  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 '

So

'''2565'',''5216'',''5364''

might work.

Did you consider to write your query with an IN clause?

Ulrich

Sir
Enthusiast

Re: INDEX function

Ulrich,

'''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.

 

Thanks

 
Supporter

Re: INDEX function

maybe like will be easier...

 

remove all internal ' from the @Group_ID list and try

where ','!!@Group_ID!!',' like ' %, ' !! trim(Group_ID) !! ', %' "

Supporter

Re: INDEX function

you can check how this works  with

 

select * 
from sys_calendar.calendar 
where  ',1,11,111,3212,' like '%,' !! trim(day_of_calendar) !! ',%'
Tags (1)
Highlighted
Teradata Employee

Re: INDEX function

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.