How to pass input parametrs for "IN" operator

How to pass input parametrs for "IN" operator

Hello People,

I am using an advance query where I am using "IN" operator.

However, when I am testing in Service Studio for multiple values, "IN" operator is not working.

But for single value it's giving result. In my browser, advance query not giving list of records with "IN" operator. I want to pass comma separated values to "IN" operator.

Please advice how to do it?

Suraj Borade
Hi Suraj,

How are you passing this value to the advanced query? Is it a parameter or you are putting the values hand-coded?
Hi Andre,

I am passing this value with the parameter. For hand-coded values it is working fine.
Hi Andre,

I am attaching screenshot. For Country field whenver I am putting "IN" operator it's not working properly. In service studio also, it gives result for Single country value and not for multiple countries. 

Please check query also.

--Suraj B
The parameter Expand Inline property is set to true? Don't forget to use EncodeSQL to pass expand inline parameters to avoid sql injection.


When I set parameter Expand Inline property to true, it gives me an error that right parenthesis is missing.

If the "Expand inline" property is False, Service Studio creates a variable for it. If you do something (in SQL Management Studio or Toad) like "WHERE tbl.Attr IN (@myVar)", that doesn't work either, as the SQL engine will try to find values matching that one variable.

If the "Expand inline" property is True, Service Studio will paste your variable's content right into the generated SQL, before it's sent to SQL Server or Oracle. So in case of "WHERE tbl.Attr IN (@myVar)", where myVar is an inline expanded variable, your variable needs to contain literal SQL code. In this case, something like "'VAL1','VAL2','VAL3'". If it's complaining about a parenthesis, you botched the code.

Hi Kilian sir,

Thanks for the information

I corrected the code. Now I am getting correct result in Service Studio but when I published my code and ran the application into the browser, it's giving an internal error.

Error is :  ORA-00936: missing expression.

I am observing strange behaviour since query is working fine in Service Studio but not working in browser.

Please advice.

I assume that you don't actually fill the parameter, and the database sees something along the lines of "WHERE value IN ()". Please debug and check the variable's value.

The problem is that EncodeSQL will escape single quote with another single quote, so if you have 'us','fr','ca' as example for the WHERE countrycode IN (@countries), as you are expand inline.  There is got to be a better way to handle WHERE field IN (@variable) with encodesql.  Can anyone share a good practice?

Hi Anthony,

This thread is over a year old now, can you create a new thread with your question so that in the future it is more easily found by people that share your concerns?