HANA - Passing string variable into WHERE IN() clause in SQL script

Question

Lets suppose I have some SQL script in a scripted calculation view that takes a single value input parameter and generates a string of multiple inputs for an input parameter in another calculation view.

BEGIN 
declare paramStr clob;
params = select foo 
         from bar 
         where bar.id = :IP_ID;

select '''' || string_agg(foo, ''', ''') || ''''
into paramStr 
from :params;

var_out = select * 
          from  "_SYS_BIC"."somepackage/MULTIPLE_IP_VIEW"(PLACEHOLDER."$$IP_IDS$$" => :paramStr);
END

This works as expected. However, if I change the var_out query and try to use the variable in a where clause

BEGIN 
...

var_out = select * 
          from  "_SYS_BIC"."somepackage/MULTIPLE_IP_VIEW"
          where "IP_IDS" in(:paramStr);
END

the view will activate, but I get no results from the query. No runtime errors, just an empty result set. When I manually pass in the values to the WHERE IN() clause, everything works fine. It seems like an elementary problem to have, but I can't seem to get it to work. I have even tried using char(39) rather than '''' in my concatenation expression, but no banana :(

Answer 1

Ok, so what you are doing here is trying to make the statement dynamic. For the IN condition, you seem to hope that once you have filled paramStr it would be handled as a set of parameters.

That's not the case at all.
Let's go with your example from the comment: paramStr = ' 'ip1','ip2' '

What happens, when the paramStr gets filled into your code is this:

var_out = select * 
          from  "_SYS_BIC"."somepackage/MULTIPLE_IP_VIEW"
          where "IP_IDS" in(' ''ip1'',''ip2'' ');

So, instead of looking for records that match IP_DS = 'ip1' or IP_DS = 'ip2' you are literally looking for records that match IP_DS = ' 'ip1','ip2' '.

One way to work around this is to use the APPLY_FILTER() function.

var_out = select * 
          from  "_SYS_BIC"."somepackage/MULTIPLE_IP_VIEW";

filterStr = ' "IP_IDS" in (''ip1'',''ip2'') ';

var_out_filt = APPLY_FILTER(:var_out, :filterStr) ;

I've written about that some time ago: "On multiple mistakes with IN conditions". Also, have a look at the documentation for APPLY_FILTER.