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:

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(‘(?，大数据啥意思，数据中国，企业管理平台软件，淘客如何推广，大数据库