cancel
Showing results for 
Search instead for 
Did you mean: 

Passing multiple values to a single input parameter

patrickbachmann
Active Contributor
0 Kudos

Hi folks,

I have a Microstrategy query successfully passing input parameter to a calculation view.  For example I can pass a movement type to a material movements calculation view input parameter.  However if I try to pick more than one movement type the query then fails; 

Generated SQL that works looks like this;

selectsum(a11.TOTALQUANTITY)  WJXBFS1
from"_SYS_BIC"."MyPackage/CA_TEST_PASS_PARAMETER"

('PLACEHOLDER' = ('$$MoveType$$', '101')


a11

When choosing more than one value in Microstrategy the SQL now fails and looks like this;

selectsum(a11.TOTALQUANTITY)  WJXBFS1
from"_SYS_BIC"."MyPackage/CA_TEST_PASS_PARAMETER"

('PLACEHOLDER' = ('$$MoveType$$', '101'),

'PLACEHOLDER' = ('$$MoveType$$', '103'))

a11

If I cut and paste the SQL and run directly in HANA studio the error is;

Could not execute 'select sum(a11.TOTALQUANTITY) WJXBFS1 from "_SYS_BIC"."MyPackage/CA_TEST_PASS_PARAMETER" ...' in 66 ms 361 µs .

SAP DBTech JDBC: [2048]: column store error: search parameter error:  [2018] A received argument has an invalid value;TABLE/VIEW parameter: ( 'PLACEHOLDER'='$$MoveType$$, 103') not supported

Is it possible to pass multiple values in a single parameter?  I'm using SP67 for this test.

Thanks,

-Patrick

Accepted Solutions (1)

Accepted Solutions (1)

patrickbachmann
Active Contributor
0 Kudos

FYI.  I created an SAP message and they responded that the following syntax should work but it does not.  Incidentally it's the same syntax that's found in the new SPS6 modeling guide;

select sum(TOTALQUANTITY) from "_SYS_BIC"."MyPackage/CA_TEST_PASS_PARAMETER"

('PLACEHOLDER' = ('$$MoveType$$', '''101'',''103'''))

They also said that it looks like Microstrategy is not generating the SQL correctly to match this syntax.  (Although in my case I'm testing this directly in studio sql editor and still does not work).  The error is;

Could not execute 'select sum(TOTALQUANTITY) from "_SYS_BIC"."MyPackage/CA_TEST_PASS_PARAMETER" ...' in 117 ms 319 µs .

SAP DBTech JDBC: [257]: sql syntax error:  [257] sql syntax error: incorrect syntax near ",": line 1 col 84 (at pos 84)Please check lines: 4,

-Patrick

former_member184768
Active Contributor
0 Kudos

ok, finally got it working.

Here are the changes / deviations from the regular development:

1) The projection filter should be something like in("CAL_WEEK",$$IP_WEEK$$). Please note, there are no single quotes around $$IP_WEEK$$, which are generally put by HANA. So you may have to remove it.

2) The placeholder syntax in the documentation seems to be incorrect. Instead of ('PLACEHOLDER' = ('$$IP_WEEK$$' = '''201201'',''201202'''))  as per documentation, you may have to provide ('PLACEHOLDER' = ('$$IP_WEEK$$', '''201201'',''201202''')). So no = sign in between the Input parameter and values, but regular , (comma).

One more thing, please ensure that your input parameter length is sufficient enough to accommodate multiple values.

But for me, the question is, how will you get the MSTR generate the query , unless you use free form SQL. But I think that is the later part.

Regards,

Ravi

patrickbachmann
Active Contributor
0 Kudos

Hi Ravi,

It's strange as it's still failing for me using this syntax;

select sum(TOTALQUANTITY) from "_SYS_BIC"."MyPackage/CA_TEST_PASS_PARAMETER"

('PLACEHOLDER' = ('$$MoveType$$', '''101'',''102'''))

The difference between my example and yours that I can see is it sounds like you are using a graphical calc view and I am using SQL script type.  My calc view called CA_TEST_PASS_PARAMETER actually contains a call to another procedure that contains the real SQL select statement.  Maybe I will change that and put my select directly in the first calculation view to simplify and try to isolate the problem.  If that does not work I will try a graphical projection as you are doing and see if that works.

Thanks

-Patrick

patrickbachmann
Active Contributor
0 Kudos

Ok I simplified and now my calc view has a simple select statement (does not call any other procedures) and now this syntax executes without an error message!

select sum(TOTALQUANTITY) from "_SYS_BIC"."MyPackage/CA_TEST_PASS_PARAMETER"

('PLACEHOLDER' = ('$$MoveType$$', '''101'',''102'''))

The BAD news is that it's returning NULL value.  Your point about making the data type long enough to except both values has me wondering if it's interpreting the MoveType value to be '101','102' as a single concatenated value which of course does not really exist. 

-Patrick

patrickbachmann
Active Contributor
0 Kudos

Ravi my calc view contains simple SQL like this;

select SUM(MENGE) As TOTALQUANTITY from "MySchema"."MSEG" WHERE "BWART" = :MoveType and "MJAHR" = '2005'

But after I pass my parameter I would guess it needs to look something like this;

select SUM(MENGE) As TOTALQUANTITY from "MySchema"."MSEG" WHERE "BWART" IN ('101','102') and "MJAHR" = '2005'

My guess is it's interpreting more like this;

select SUM(MENGE) As TOTALQUANTITY from "MySchema"."MSEG" WHERE "BWART" = '101,102' and "MJAHR" = '2005'

But I have to poke around some more, I am probably missing something simple...

-Patrick

patrickbachmann
Active Contributor
0 Kudos

Hey Ravi, if you have any time can you try using a script type view instead of graphical?  I think there could be a bug;  for example inside my script calc view is very simple statement like this;

/********* Begin Procedure Script ************/

BEGIN

var_out = select SUM(MENGE) As TOTALQUANTITY from "MYSCHEMA"."MSEG" WHERE "BWART" IN (:MoveType) and "MJAHR" = '2005';

   

END /********* End Procedure Script ************/

I think it's interpreting like this which is returning NULL result if I try via SQL editor directly;

select SUM(MENGE) As TOTALQUANTITY from "MYSCHEMA"."MSEG" WHERE "BWART" IN ('''101'',''102''') and "MJAHR" = '2005'

When it should be interpreting like this which returns correct result in SQL editor directly;

select SUM(MENGE) As TOTALQUANTITY from "MYSCHEMA"."MSEG" WHERE "BWART" IN

('101','102') and "MJAHR" = '2005';

-Patrick

former_member184768
Active Contributor
0 Kudos

Hi Patrick,

By any chance, can you use CE_PROJECTION for the filter implementation. If you can, then the filter can be defined as

  var_out = CE_PROJECTION

         ( :vty

         , ["ZWEEK"

         , "ZROWCOUNT"]

         , 'in("ZWEEK", :ip_week)'

         );

Then the following statement will work.

SELECT "ZWEEK", sum("ZROWCOUNT")

FROM "_SYS_BIC"."mypkg.filterview/ZTEST_FILT"

('PLACEHOLDER' = ('$$ip_week$$', '''201202'', ''201203'''))

GROUP BY "ZWEEK";

But I could not get it working with SELECT statement in the scripted view as you mentioned.

Regards,

Ravi

patrickbachmann
Active Contributor
0 Kudos

Ok Ravi, thanks I take some solace in the fact that you also experience the same thing with the script!  Ok let me try that method next and see.

-Patrick

patrickbachmann
Active Contributor
0 Kudos

Hi Ravi, I'm still tinkering with projection method (busy day here) and will close this thread soon but I got this back from SAP on the subject which may be interesting to you....

"HANA development support confirmed that the modeller guide, which is

where multi-value parameters are documented, refers to CE_PROJECTION and that the SQL Script guide itself does not mention multi-value parameters as it is not supported using the tradition SELECT..WHERE clause/IN clause filtering.


You may want to look into passing your string in with a delimiter and

using a 'split' type of procedure/function to handle the mutli-value

aspect, or to write your calculation view using the CE_* functions. "

-Patrick

former_member184768
Active Contributor
0 Kudos

Hi Patrick,

Couple of thoughts:

  1. How will you get the multiple value Input parameter in MSTR. Currently, there is very little or no control on the behavior of Input parameters in MSTR.
  2. May be, I did not understand your requirement completely, but why do you need Input parameter in the first place. If your requirement is as you mentioned, then it can be implemented with any filter in MSTR. That filter will anyway be passed as WHERE condition and is likely to be pushed down in HANA. So why is the real need for the Input parameter itself.

Regards,

Ravi

patrickbachmann
Active Contributor
0 Kudos

Hi Ravi,

1. I'm not exactly sure, our Microstrategy guy is looking into this. He said he is testing using db query tool that is bundled with Microstrategy.

2. As with most of my questions here, my example is actually much more simple than my real life problem.  Usually I make it more simple so that it's easier for others to understand and help.  Of course this comes with great risk as it could get Lars to appear and ask why you are doing such a dumb simple thing in such a manner (but that's another story).    In my case I have a lot of metrics that are being passed to several other calculation views and need to affect the query before the materialization step.

PS:  Finally about to experiment with CE_PROJECTION now!  Will let you know results soon.

-Patrick

patrickbachmann
Active Contributor
0 Kudos

Ravi alas it worked using projection!

Thanks for all of your help. 

-Patrick

Former Member
0 Kudos

Tou can do this by creating it as an array, unnesting it and passing it as a local table input variable, and then do

IN (SELECT * FROM :lt_temp)

But, all of these are executed in a very inefficient way by the calculation engine.

John

Former Member
0 Kudos

Hi Ravi, This approach didn't work for me.I am Using Script Based Calculation View as below. ---------------------------------------------------- BEGIN var_defects = CE_JOIN_VIEW("_SYS_BIC"."ABCAT_DEFECTS",["EmployeeName","ContextId"]); var_ff = CE_AGGREGATION(:var_defects, [COUNT("ContextId") AS "Defects"],["EmployeeName"]); var_out=CE_PROJECTION(:var_ff,["Defects","EmployeeName"], 'IN("EmployeeName", :EMPNAME)'); END /********* End Procedure Script ************/

Input Parameter Details;

On DataPreview, I got the below error.

Please let me what is the issue with this?

regards

Raj

9980016331

Answers (2)

Answers (2)

Former Member
0 Kudos

This has the potential of performing badly, but it should work:

WHERE ',' || :MoveType || ',' LIKE '%,' || BWART || ',%'

Here is what the concatenation accomplishes. When '101,102' is passed, we first get it to look like ',101,102,' to make sure a BWART of '1101' is not matched. This could be done earlier in the code, along with some validation to make sure it contains what you expect it to contain, so as to avoid sql injection.

We then compare that to the result of concatenating wildcards and delimiters around the content of BWART. so if BWART contains 102, this is the comparison done:

WHERE ',101,102,' LIKE '%,102,%'

which should pass.

A BWART of 103 should fail:

WHERE ',101,102,' LIKE '%,103,%'

I think the best solution would be a table type input parameter, but I am not sure what MSTR would do with that.

patrickbachmann
Active Contributor
0 Kudos

Bob, welcome to the SCN community!  It's been a while since i looked at this but will tinker with it when I get a chance.

-Patrick

patrickbachmann
Active Contributor
0 Kudos

Ravi, in your CE_PROJECTION example, how are you setting your table variable for :vty?

var_out = CE_PROJECTION

         ( :vty

         , ["ZWEEK"

         , "ZROWCOUNT"]

         , 'in("ZWEEK", :ip_week)'

         );

The reason I ask is because I was setting mine something like this;

:vty = select ZWEEK, ZROWCOUNT from MyTable

var_out = CE_PROJECTION

     (:vty, etc

But I've been reading how it's not good to mix SQL with CE engine functions.  So I'm not clear on how I can set :vty to a table without using the select for example.

-Patrick

former_member182114
Active Contributor
0 Kudos

Hi Patrick / Ravi,

There's a comparation with apples and oranges here.

On CE_PROJECTION the in function is doing a "Is part string "ZWEEK" inside complete string ":ip_week" ?

If you have ZWEEK = 1 you got true as ''101'',''102'', same for 0, 2, 10, 01,101,.....

On the SQL side, you used an operator IN which try is left operator inside one of parameters on left. Due to this result is always false except you have ''101'','102'' on left side.

To make SQL work as like CE_PROJECTION is working you can use where LOCATE("ZWEEK",:ip_week) > 0, but again it the result is incorrect as you will look for partial operations.

As far as I know currently there's no support for multiple values on parameters. Parameters are single value. You can do the trick passing a 'xxx' + logicalParm1 + 'xxx' + logicalParm2 + 'xxx' + logicalParmN + 'xxx' and use locate or in to catch a LOCATE/IN('xxx' + myVariable + 'xxx', parm)

What I found accepting multiple values are variables and they aren't converted to PLACEHOLDERS but WHERE clause.

Regards, Fernando Da Rós

patrickbachmann
Active Contributor
0 Kudos

Ravi, also to answer one of your questions about how this will work in Microstrategy; I just heard back from my Microstrategy developer and he is trying MSTR Freeform SQL query with syntax like this;

select (sumPAR_TEST.TOTALQUANTITY TOTALQUANTITY

from "_SYS_BIC"."MyPackage/CA_TEST_PASS_PARAMETER"

('PLACEHOLDER' =('$$MoveType$$', '[Movement Type]')) PAR_TEST

In this example [Movement Type] is the microstrategy prompt.  Unfortunately though it translates like this which is missing extra single quotes around each value;

select     sum(PAR_TEST.TOTALQUANTITY)  TOTALQUANTITY

from     "_SYS_BIC"."development.pr1959/CA_TEST_PASS_PARAMETER"

('PLACEHOLDER' = ('$$MoveType$$', ''101', '102''))   PAR_TEST

instead of what we need which is;

('PLACEHOLDER' = ('$$MoveType$$', '''101'', ''102'''))   PAR_TEST

So at this point we are not sure if this will be possible or not.

-Patrick

former_member182114
Active Contributor
0 Kudos

Hi Patrick,

Sorry to enter in this discussion too late, but if you are receiving the string content '101', '102' on parameter MoveTypes.

You have the option to filter using IN on CE scripting or with LOCATE on SQL.

Need to concatenate single quote on your variable before in operation and need to keep in mind that this approach will end in a very bad performance if your used on a many rows dataSource.

CE_PROJECTION(...

' in(''''' + "MOVETYPE" + ''''','''$$MoveType$$''') ');

WHERE LOCATE( ''' || "MOVETYPE" || ''', '$$MoveType$$' ) > 0;

Regards, Fernando Da Rós

patrickbachmann
Active Contributor
0 Kudos

Hi Fernando,

It's better late than never.    Now, just to clarify;  are you saying that BOTH the IN option and the LOCATE option both perform very badly?  Or just the LOCATE is poor?

Thanks,

-Patrick

former_member182114
Active Contributor
0 Kudos

Hi Patrick,

Both are doing same thing, doing a string operation to identify if you want or not that row.

So there's no push down working here, they will perform this action for all rows... If your dataset to test is huge you may experiment delay in the response.

Anyhow, it's not like you can't use it but you need to test.

Regards, Fernando Da Rós

former_member184768
Active Contributor
0 Kudos

Hi Patrick,

In my example, I was using CE functions to get the table variable. Since it was just a test for CE_PROJECTION. With Freeform SQL, I think the query would be very much static and there may not be any option for additional drilldown. It may work if you are using it for the dashboard and any further navigation (after the result set is fetched) will not generate another SQL statement.

Regarding combining SQL and CE functions, I agree with you. The key point from my perspective is to ensure that the filters are pushed down. As you mentioned, the real need for using the projection is to ensure that the performance is enhanced. Based on the execution plan, you may have to adopt the appropriate approach.

Regards,

Ravi

former_member182302
Active Contributor
0 Kudos

Hi Patrick,

Just adding my views here to add one more way on doing the filtering for Multiple Values:

Instead of using Locate ( as the performance is bad) and because we don want to combine SQL and CE functions, we went for Procedures to do this using "Replace" function to split and forming the "IN" condition as shown below.

http://scn.sap.com/community/hana-in-memory/blog/2013/12/17/sap-hana-handling-dynamic-select-column-...

Regards.

Krishna Tangudu

patrickbachmann
Active Contributor
0 Kudos

Hi Krishna,

Thanks for your comments.  It's an interesting thread.

-Patrick

Former Member
0 Kudos

Can you please guide on how to pass the multiple entries enabled input parameter to the calculated measure? I am not able  to do
so.

Example below-

Calculated measure

QTY_UNALLOCATED_NA

Formula

if("MP_WERKS"='$$PAR_WERKS1_NA$$',QTY_UNALLOC,0)

Input Parameter
'$$PAR_WERKS1_NA$$' is defined as Parameter Type column.

Reference col:
MP_WERKS (field)

So when I pass"Plant1" in the input parameters #$$PAR_WERKS1_NA$$' I get data.

When I pass  "Plant1","Plant2"(multiple entries) #$$PAR_WERKS1_NA$$' I do not get data.

Can you please please guide, this is urgent for us.