How to pass multiple values in input parameter in calculation views

[Former Member]

Hi,

I have created a calculation view and need to pass multiple values in its input parameter. Calculation view syntax is:

select * from "_SYS_BIC"."fingerprint/GET_CREATIVE_DATA"('PLACEHOLDER' = ('$$PARAM1$$', 'value1,value2'));

The value1 and value2 are strings and i need to search in my query using IN example:

select * from tablename where column IN('value1','value2');

I tried the following way but it did not work:

select * from "_SYS_BIC"."fingerprint/GET_CREATIVE_DATA"(PLACEHOLDER' = ('$$PARAM1$$,'''201202'', ''201203''') ;

Any  suggestions?

Thanks

Unnati

[rindia]

Hi Unnati,

Change it as

PLACEHOLDER' = ('$$PARAM1$$,'201202'', ''201203') ;

If Select * wont work then select some columns.

Below is the example:

Regards

Raj

[Former Member]

Hi Raj,

Thanks for the reply but it didn't work.

[rindia]

Unnati,

Can you send the screenshot of your query you used and the error.

What is type of your input parameter, VARCHAR or something else?

Regards

Raj

[rindia]

I guess you might be missing GROUP BY for selected columns for SELECT clause.

[Former Member]

Hi Raj,

Thanks for your reply's. Please find below the details u asked for.

Query in calculation view:

select COL1,COL2 from "SCHEMA"."TBL_HISTORY" WHERE COL1 IN (:PARAM1) limit 10;

Executing the view as:

select COL1,COL2 from "_SYS_BIC"."schema/GET_ARRAY_PARAM_TEST" ('PLACEHOLDER'=('$$PARAM1$$','val1","val2')) group by COL1,COL2;

No error is being displayed but empty result. On executing the query in calculation view results are returned but when executing through calculation view it's not displaying them.

Data type of input parameter is VARCHAR.

[former_member182302]

Hi Unnati Nigam,

I hope the blog i have written on this specific area might help you.

Please note that i have used Calculation view (Graphical) here.

I am expecting that you might want to revisit the expression you have written for "Filtering" logic in your projections.

If you are following Script based approach,Then you might want to filter like this

('$$PARAM1$$','''val1'',"val2'''))

Your SQL statement will look like below:

select COL1,COL2 from "_SYS_BIC"."schema/GET_ARRAY_PARAM_TEST" ('PLACEHOLDER'=('$$PARAM1$$','''val1'',"val2''')) group by COL1,COL2;

also have a look on the other thread which discusses the same functionality.

Hope this clarifies your question.

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna,

Thanks for the reply. The blog is very useful but i am using Calculation View (SQL Script) and not Graphical View.

Regards,

Unnati

[former_member182302]

Hi Unnati,

So is the following approach as mentioned above if you are using SQL based also didnt work?

If you are following Script based approach,Then you might want to filter like this

('$$PARAM1$$','''val1'',"val2'''))

select COL1,COL2 from "_SYS_BIC"."schema/GET_ARRAY_PARAM_TEST" ('PLACEHOLDER'=('$$PARAM1$$','''val1'',"val2''')) group by COL1,COL2;

Can you place the SQL you are using in your view??

Also have a look on the this thread also

Passing multiple values to a single input parameter

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna,

Here is the sql used in calc view :

select COL1,COL2 from "SCHEMA"."TBL_HISTORY" WHERE COL1 IN (:PARAM1) limit 10;

I have already tried the way you mentioned,but it didn't work.

Regards,

Unnati

[former_member182302]

Hi Unnati,

You can use CE_Projection and pass multiple parameters

but as mentioned by you if you are using SQL based approach, you have to do the following:

1) You need to split the values received in the input parameters and use it. For this you may need to use Procedure as mentioned in the below document.

2) We can use "Locate" in the select query in the SQL Script inside view:

select region_cd from "TABLE"

where locate(:Region,REGION_CD)>0;

GROUP BY region_cd

In the above statement instead of 'APAC,AMR' you can your input parameter now and pass the value in the input parameter as shown below:

SELECT * FROM VIEW ('PLACEHOLDER' = ('$$Region$$', 'AMR'',''APAC')) ;

Above one might give you some performance implications as "locate" will not actually pushdown the filter logic, Which we actually want.

3) Based on your reporting tool if it is HTML5 or BO, we can further discuss on how to handle the input parameter from the front end.

Regards,

Krishna Tangudu