Skip to Content

Hi All,

I’m Mohammad Mujahid, SAP HANA Developer, this small article is about working with Multi value or multi entry input parameter in Scripted calculation view.

Creating multi-value or multi entry input parameter in HANA Graphical calculation view is soothing and works perfectly, however doing same in Hana Scripted view doesn’t work correctly (Checked on HANA 01 SPS 12, not sure if SAP has fixed this in later versions).

Let’s see how to do this in Scripted View.

creating sample table ZSALES_TEST with Year, Country and Amount columns

CREATE COLUMN TABLE “MOMUJAHID”.”ZSALES_TEST” (“YEAR” VARCHAR(4),
“COUNTRY” VARCHAR(25),
“AMOUNT” INTEGER CS_INT) UNLOAD PRIORITY 5 AUTO MERGE;

Inserting sample data to table:

insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2015′,’India’,5000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2016′,’India’,8000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2017′,’India’,15000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2018′,’India’,25000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2015′,’UAE’,5000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2016′,’UAE’,15000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2017′,’UAE’,35000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2018′,’UAE’,55000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2015′,’US’,15000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2016′,’US’,105000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2017′,’US’,20000);
insert into “MOMUJAHID”.”ZSALES_TEST” values(‘2018′,’US’,515000);

table data:

YEAR COUNTRY AMOUNT
2015 India 5,000
2016 India 8,000
2017 India 15,000
2018 India 25,000
2015 UAE 5,000
2016 UAE 15,000
2017 UAE 35,000
2018 UAE 55,000
2015 US 15,000
2016 US 105,000
2017 US 20,000
2018 US 515,000

 

Create a new Scripted calculation view in HANA to fetch values from above table with multi value input parameter.

Below multi value input parameter(IP_YEAR) is created in a scripted view

 

declare two variables VAR_YEAR  to store input parameter value and COUNT_YEAR to store no. of items entered in input parameter.

Use below code:

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

BEGIN

DECLARE VAR_YEAR NVARCHAR (100);

DECLARE COUNT_YEAR INTEGER;

VAR_YEAR = :IP_YEAR;
–Input parameter returns string as ”’2017”,”2018”’ this should be replaced with 2017,2018

VAR_YEAR = REPLACE(REPLACE(VAR_YEAR,  ”’,”’, ‘,’),””,”);

–Counting no. of items entered, adding +3 just be sure

SELECT (LENGTH(VAR_YEAR)-LENGTH(REPLACE(VAR_YEAR, ‘,’,”))+3) INTO COUNT_YEAR FROM DUMMY;

–below code returns input paramter values in a table format

T_YEAR= SELECT “VAR_YEAR” AS YEAR, LENGTH(VAR_YEAR) as RANK FROM

(

SELECT

SUBSTR_REGEXPR(‘(?<=^|,)([^,]*)(?=,|$)’ IN :VAR_YEAR OCCURRENCE “SERIES”.”ELEMENT_NUMBER” GROUP 1) “VAR_YEAR”

FROM DUMMY,

SERIES_GENERATE_INTEGER(1, 1, :COUNT_YEAR) “SERIES”

)

WHERE VAR_YEAR IS NOT NULL;

var_out = SELECT “YEAR”, COUNTRY, AMOUNT FROM “MOMUJAHID”.”ZSALES_TEST” where YEAR” IN (select YEAR from :T_YEAR)

ORDER BY “YEAR”;

END

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

 

Data Preview: (entered two lines for input parameter)

Output:

 

 

Cons:

  1. You need to increase input parameter data type length in order to work, I have given 400 else it will throw string too long error.
  2. If input parameter value is derived from a table column or view, then this will not work, because data type value for input parameter will also be derived from table column data type

 

Hope this helps. Thanks

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply