Problem setting SQL sentence "SELECT... IN " in Query database

Question

Hi there, 
 I have problem setting up the SQL sentence "SELECT .... IN " in query database smart service. 
 if I directly writing "SELECT .... IN ('1234','5678')" in the SQL Statement, it will work. 
 if I have create a process variable to be the string '1234','5678', then put the following sentence 
 SELECT .... IN (ac!str) and assign the process variable to this ac!str, it will not work. 
 
 I dont know what's the problem here, as I can't hard coded in the SQL sentence, the string need to be dynamic. 
 Can anyone be able to help? 
 regards, 
 Lin

Chris · Answer

What type of database are you using? In Oracle, we work around this by utilizing INSTR() such as: 
 SELECT ...
WHERE INSTR(ac!list, ',' || table.column || ',') > 0 
 The ac! value in your Data Inputs tab is then defined as: 
 ="," & joinarray(pv!list, ",") & "," 
 Where pv!list is the multiple variable that you are looking to search on.

Chris · Answer

Yes, INSTR() is an Oracle-only function. For MSSQL Server, you will likely be switching to CHARINDEX() .