Skip to Content
0

Multi-value input parameter in SQLScript Procedure

May 24, 2017 at 09:56 PM

178

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Florian Pfeffer
May 31, 2017 at 06:59 PM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Gopinath Kolli May 31, 2017 at 04:00 PM
1

Give input parameters in this format

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Gopi

That works with fixed set of values , I am unable to pass a variable there .

Regards

Anindya

0
Anindya Bose May 31, 2017 at 07:17 PM
0

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

Syntax worked . Many thanks Florian.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Did you get this to work? I tried it calling an SQLScript Calc View and it would not work.. thanks

0