SAP HANA passing value from one input parameter to other for filtering table

I am going to explain how to pass value from one input parameter to other parameter and filter the underlying table without any process change in the view.

CV_xxx_BASE View before changes

Configuration of input parameter in current view was as under

Parameter Type : Direct

Semantic Type : Date

Data Type : Date

Table is getting filter from input parameter IP_ASOFDATE

Business Requirement 

Changes asked by business,

1. Date description rather than date as input parameter i.e instead of 05/11/2017 business want to see ‘TODAY’.
2. Date can be passed as free text  i.e 05/11/2017 OR 05.11.2017 etc any date format.

Issue

1. To get dynamic Date with Description from system date without maintenance so that you can use input parameter.
2. Date as Free Text check if date is valid or not.
3. Converting Date Description back to date so that respective columns can be filtered on basis of Date.
4. There should be no changes in the structure, process or flow of the view.
5. No additional view can be created as there should not be any additional maintenance.

Solution

1. To get dynamic date with description from system date.

Create a Scripted calculation view CV_DATES_HELP with following code

BEGIN

DD = SELECT 1 UID , 'TODAY' DAY_PICK , CURRENT_DATE DATES FROM DUMMY union

     SELECT 2 UID , 'Last Day Of Month' DAY_PICK , LAST_DAY(CURRENT_DATE) DATES FROM DUMMY union

     SELECT 3 UID, 'First Day Of Month' DAY_PICK , ADD_MONTHS(NEXT_DAY(LAST_DAY(CURRENT_DATE)),-1) DATES FROM DUMMY union

     SELECT 4 UID, 'Last Day Of Last Month' DAY_PICK , LAST_DAY(ADD_MONTHS( CURRENT_DATE,-1)) DATES FROM DUMMY union

     SELECT 8 UID, 'Last Day Of The Year' DAY_PICK , TO_DATE ((SUBSTRING(CURRENT_DATE,0,4))||'-12-31', 'YYYY-MM-DD') DATES FROM DUMMY union

     SELECT 9 UID, 'First Day Of The Year' DAY_PICK , TO_DATE ((SUBSTRING(CURRENT_DATE,0,4))||'-01-01', 'YYYY-MM-DD') DATES FROM DUMMY;

-- Can keep on adding Dynamic Date as per your project Need

var_out =  SELECT UID,DAY_PICK,DATES , (DAY_PICK || ' ' || DATES) DATEKEY FROM :DD;

END

CV_DATES_HELP will give DAY_PICK which business can use and Date which you can pass to other input parameter.

Result

2.  To check whether a string is valid date or not I am using a ISDate function

3. Stored procedure to convert Day description back to Date code as under

CREATE PROCEDURE "SchemaName"."IP_ASOF" (IN  IP_ASOF NVARCHAR(100), out IP_ASOFDATE NVARCHAR(10)) 

LANGUAGE SQLSCRIPT 

AS 

--CALL "SchemaName"."IP_ASOF"('TODAY',?) ;

BEGIN 

DECLARE VCOUNT INT ;

DECLARE DT NVARCHAR(10);

SELECT COUNT(*) INTO VCOUNT  FROM "_SYS_BIC"."PackageName/CV_DATES_HELP"

where DAY_PICK = UPPER(:IP_ASOF); 

--IF INPUT PARAMETER IS ONE OF 74

IF :VCOUNT > 0 THEN

SELECT DISTINCT DATES INTO IP_ASOFDATE FROM "_SYS_BIC"."PackageName/CV_DATES_HELP"

WHERE DAY_PICK = UPPER(:IP_ASOF);

ELSE 

 SELECT DISTINCT "_SYS_BIC"."ISDATENEW"(:IP_ASOF) INTO IP_ASOFDATE from dummy;

END IF;

END;

4. Passing values from input parameter to other parameter (Parameter Mapping)

1. In below images will show the changes made to input parameter IP_ASOFDATE initially was Direct parameter with Date datatype now it is changed to column getting value from CV_DATES_HELP view.

2.  One more input parameter IP_ASOF is added which is using stored procedure IP_ASOF

3. Do Parameter Mapping IP_ASOFDATE to IP_ASOF

4. Once you have mapped the parameter then you have to change the filter expression from input parameter IP_ASOFDATE to IP_ASOF because now IP_ASOFDATE is a varchar date description, whereas IP_ASOF is the parameter which will get proper date value which will filter the table.

Then activate the view…

Result

This way now even when you pass Date description in data preview editor or from front end stored procedure will convert date description or date as free text to date and pass on to input parameter IP_ASOF which eventually filter the table.

You can filter view by Date Description or Date as free text..

1. Parameter with Date Description

Result

2. Date as free text

Result