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 :(

what error message do you get in this case? Also: there is a double quote missing from your view name. And what are you trying to do here? Either you feed your values into input parameters or variables - these are not exchange-able. — Lars Br., Mar 05 '17 at 23:08
Sorry, I fixed the typo's. I don't get any activation or runtime error, I just don't get any results back when I query the scripted calc view, yet when run a query manually with the form `WHERE "SOME_COLUMN" IN('ip1','ip2')` it works fine. And like I said, using this implementation with IP's like the first example works great. — Jenova, Mar 05 '17 at 23:59
Not that it pertains to this question but I have complex services which the HANA optimizer does not push filters down properly. For example, if I had a service to calculate your balances for a single loan number the HANA optimizer works great, but there is no way to transform the input to pass in multiple loan numbers for a particular account number. Graphically joining this transformation does not work without a huge performance loss. Instead, I wrap graphical views in scripted calc views to transform inputs and query the original view with multiple parameters without sacrificing performance. — Jenova, Mar 06 '17 at 00:02
Sounds like you're not doing it right. Scripted calcviews have been deprecated for some time now and table functions should be used instead. If you must use graphical calc views with multiple parameters and dynamic invocation (essentially what you asked about) then see my blog post. — Lars Br., Mar 06 '17 at 09:18
I have been staying clear of using dynamic invocation because of rumoured performance losses of using dynamic sql, but it seems using a dynamic `IN` statement has comparable losses. Thank you for the advice, I will look into it. — Jenova, Mar 06 '17 at 15:21

score 2 · Answer 1 · answered Mar 06 '17 at 06:23

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.

That worked, but now I am getting terrible performance on the first execution and when I manually execute the same query it is lightning fast... — Jenova, Mar 06 '17 at 15:11

lucodealethea · Answer 2 · 2017-09-17T21:21:05.380

The SAP note “2315085 – Query with Multi-Value Parameter on Scripted Calculation View Fails with Incorrect Syntax Error“ actually showcases the APPLY_FILTER() approach that failed when I first tried it.

It also presents an UDF_IN_LIST function to convert a long varchar string with array of items from input parameter to a usable in-list predicate.

Unfortunately, couldn't make it work in sps11 rev 111.03 despite some parameter available (SAP Note 2457876 :to convert error into warning for string length overflow) - (range 3) string is too long exception

then ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'System') set ('sqlscript', 'typecheck_procedure_input_param') = 'false' WITH RECONFIGURE;

-recompile the Calc View

then

select * from :var_tempout where OBJECT_ID in (select I_LIST from "BWOBJDES"."CROSS_AREA::UDF_INLIST_P"(:in_objectids,','));

invalid number exception - invalid number

But taking the scripting out of the function makes it work...

For this SPS 11 level APPLY_FILTER seems to be the only workaround. And it is really hard to say what would be the rev on SPS 12 to go in order to have it.

FUNCTION "BWOBJDES"."CROSS_AREA::UDF_INLIST_P"(str_input nvarchar(5000), 
delimiter nvarchar(10)) 
RETURNS table ( I_LIST INTEGER ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
/********* Begin Function Script ************/
BEGIN
DECLARE cnt int;
DECLARE temp_input nvarchar(128);
DECLARE slice NVARCHAR(10) ARRAY;

temp_input := :str_input;
cnt := 1;
WHILE length(temp_input) > 0 DO
    if instr(temp_input, delimiter) > 0 then
        slice[:cnt] := substr_before(temp_input,delimiter);
        temp_input := substr_after(temp_input,delimiter);
        cnt := :cnt + 1;
    else
        slice[:cnt] := temp_input;
        break;
    end if;
END WHILE;
tab2 = UNNEST(:slice) AS (I_LIST);
return select I_LIST from :tab2;
END;

CREATE PROCEDURE "MY_SCRIPTED_CV/proc"( IN numbers NVARCHAR(5000), OUT 
var_out
MY_TABLE_TYPE ) language sqlscript sql security definer reads sql data with 
result view
"MY_SCRIPTED_CV" as
/********* Begin Procedure Script ************/
 BEGIN
  -- not working
  --var_out = select * from MY_TABLE where NUMBER in (select I_LIST from 
  --UDF_INLIST_P(:numbers,','));

  -- working
  DECLARE cnt int;
  DECLARE temp_input nvarchar(128);
  DECLARE slice NVARCHAR(13) ARRAY;
  DECLARE delimiter VARCHAR := ',';

  temp_input := replace(:numbers, char(39), '');
  cnt := 1;
  WHILE length(temp_input) > 0 DO
    if instr(temp_input, delimiter) > 0 then
        slice[:cnt] := substr_before(temp_input,delimiter);
        temp_input := substr_after(temp_input,delimiter);
        cnt := :cnt + 1;
    else
        slice[:cnt] := temp_input;
        break;
    end if;
  END WHILE;
 l_numbers = UNNEST(:slice) AS (NUMBER);

 var_out=
 SELECT *
 FROM MAIN AS MA
 INNER JOIN l_numbers as LN
 ON MAIN.NUMBER = LN.NUMBER

 END;

score 0 · Answer 3 · answered Apr 23 '19 at 14:45

I know, this is a quite old thread but nevertheless my findings based what I read in here from Jenova might be interesting for others. Thatswhy I am writing them down.

I was faced with the same problem. Users can sent multiple entrie in an input parameter in a calc view I have to optimize. Unluckily I run into the same problem like Jenova and others before. And, no, imho this is not about dynamic view execution or dynamic sql, but about processing in lists in a clean way in a scripted calc view or table function if they are send in an input parameter.

Lars' proposal to use APPLY_FILTER is not applicable in my case, since I cannot use a complex statement in the APPLY_FILTER function itself. I have to materialize the whole amount of data in the select, which result I can assign to APPLY_FILTER and then execute the filtering. I want to see the filtering applied in the first step not after materializing data which is not appearing in the result of the filter application.

So I used a hdbtablefunction to create a function performing the parameter string cleanup and the transformation into an array and afterwards it is returning the result of the UNNEST function.

Since the result of the function is a table it can be used as a table inside the scripted view or tablefunction - in joins, subselects and so on.

This way I am able to process user input lists as expected, fast and with much less resource consumption.

FUNCTION "_SYS_BIC"."package1::transparam" (ip_string NVARCHAR(500) ) 
        RETURNS table ( "PARAMETER" nvarchar(100))
        LANGUAGE SQLSCRIPT
        SQL SECURITY INVOKER AS
        v_test varchar(1000);
        IP_DELIMITER VARCHAR(1) := ',';
        v_out VARCHAR(100):='';
        v_count INTEGER:=1;
        v_substr VARCHAR(1000):='';
        v_substr2 VARCHAR(1000):='';
        id INTEGER array;
        val VARCHAR(100) array;
    BEGIN 
    --
     v_substr:=:ip_string; 
     v_substr := REPLACE(:v_substr, '''', '');
     v_substr := REPLACE(:v_substr, ' ', '');
        while(LOCATE (:v_substr, :ip_delimiter) > 0 ) do

            -- find value
            v_out := SUBSTR(v_substr, 0, LOCATE (:v_substr, :ip_delimiter) - 1 );

            -- out to output
            val[v_count]:=v_out;

            -- increment counter
            v_count:=:v_count+1;

            -- new substring for search
            v_substr2 := SUBSTR(:v_substr, LOCATE (:v_substr, :ip_delimiter) + 1, LENGTH(:v_substr));
            v_substr := v_substr2;

        END while;

        IF(LOCATE (:v_substr, :ip_delimiter) = 0 AND LENGTH(:v_substr) > 0) THEN
            -- no delimiter in string
            val[v_count]:=v_substr;

        END IF;

        -- format output as tables
          rst = unnest(:VAL) AS ("PARAMETER");

       RETURN SELECT * FROM :rst;
    END;

can be called like

select * from "package1.transparam"('''BLU'',''BLA''')

returning table with two lines

PARAMETER
---------
BLU
BLA

score 0 · Answer 4 · answered May 12 '20 at 12:02

The most comprehensive explanation is here: https://blogs.sap.com/2019/01/17/passing-multi-value-input-parameter-from-calculation-view-to-table-function-in-sap-hana-step-by-step-guide/

Creating custom function splitting string into multiple values

Then inner/left outer join can be used to filter.

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

4 Answers4