Skip to Content

Multi-value input parameter in SQLScript Procedure

Hi Folks

I am trying to use a Graphical calculation view with input parameter inside a SQLScript Procedure . ( Basically I am trying to write an AMDP ) . This works for single value Input parameter but not getting the syntax for multi value parameter . Can someone please help ?

My objective is to take Multiple value in $$IP_MONTH$$ , like

IN ($$IP_MONTH$$, V1,V2,V3 ) where V1, V2, V3 would be different months .

     RS_OP = SELECT "USERNAME","MATERIAL", sum("NETVAL_INV") AS "NETVAL_INV"


         FROM "_SYS_BIC"."ZXXX/ZCV_AO_TOP_MATERIAL" (
        PLACEHOLDER."$$IP_USER_NAME$$"=>:V_USER ,
        PLACEHOLDER."$$IP_SOLD_TO$$" => :SOLD_TO,
        PLACEHOLDER."$$IP_MONTH$$" =>'201601',
        PLACEHOLDER."$$IP_TOP_X$$"=>:TOP_X  )

Thanks in advance !

Cheers

Anindya

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 31, 2017 at 06:59 PM

    Actually you have to create a variable in your SQLScript which holds all values, because the multi-value passed as comma separated list.

    As your month is a character based value (according to your example above) you have to consider that the single quotes have to be escaped in the string.

    For instance (of course the variable has to be filled e.g. in a loop or by a select statement depending on your source):

    declare lv_month_multi_values nvarchar(1000);
    
    -- to be filled dynamically
    lv_month_multi_values := '''201601'',''201602''';
    
    SELECT ...
    ( ...
    PLACEHOLDER."$$IP_MONTH$$" => :lv_month_multi_values
    ...)

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded

  • May 31, 2017 at 04:00 PM

    Give input parameters in this format

    ('PLACEHOLDER' = ('$$ip$$', '''0500000000'',''0500000001'''))

    Add comment
    10|10000 characters needed characters exceeded

  • May 31, 2017 at 07:17 PM

    Thanks Florian. Let me try that syntax and let you know the result .

    Syntax worked . Many thanks Florian.

    Add comment
    10|10000 characters needed characters exceeded