Multi line Input Paramters with Scripted View

[former_member590971]

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