Raj K

SAP HANA Lead Consultant, Century Link

SCN Profile

Raj Kumar Salla is a Lead SAP HANA Consultant and good at Modeling SAP HANA information views.

This document is prepared on HANA SPS 12 based on this thread.

 

Use case: The user is asked to enter a date. If nothing is specified then it should be default to first day of the current month else user specified value should be used to filter the data, using Graphical calculation view.

 

If you are thinking to do by using Input parameter with “Derived from Procedure/Scalar function”

then you are almost there with just 2 hurdles to cross.

 

For this demonstration I’m using the below table structure for which the field

DOJ is of type DATE on which the input parameter will be used.

1.TableStructure.jpg

 

Sample Data in the table:

 

2.SampleData.jpg

 

Create a procedure which returns the date (first day of current month).
CREATE PROCEDURE RAJ.FIRST_DAY (OUT FIRST_DAY DATE)

LANGUAGE SQLSCRIPT

AS

 

BEGIN

—- Write the logic based on business requirement.

—- This logic gives you the first day of the current month as date

SELECT ADD_DAYS(ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1),1)

INTO FIRST_DAY

FROM DUMMY;

END

;

Call the procedure to see the output:

 

CALL RAJ.FIRST_DAY(?);

3.ProcOutput.jpg

In graphical calculation view, Create Input parameter which is based on above procedure.

Now you will come across the first hurdle

 

Error message is – Procedure must have scalar parameter of type String, which is because of the product limitation.

 

4.InputParameterDateNotAllowed.jpg

The input parameter is based on Date and there is a product limitation to use string type only.

Fortunately the dates are given in single quotes in a SQL query, hence this should not stop us to go ahead.

 

5.DatesinQuotes.jpg

 

Let us go back to Procedure and change the

out parameter type from DATE to VARCHAR(10) and

convert the date output to string using TO_CHAR.

 

DROP PROCEDURE RAJ.FIRST_DAY;

CREATE PROCEDURE RAJ.FIRST_DAY (OUT FIRST_DAY VARCHAR(10))

LANGUAGE SQLSCRIPT

AS

 

BEGIN

    SELECT TO_CHAR(ADD_DAYS(ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1),1))

INTO FIRST_DAY FROM DUMMY;

END

;

 

Now in the input parameter, give the procedure name. This time it should accept

without error message. Hurdle number 1 crossed

 

6.ProcTypeString.jpg

 

Apply the input parameter on your required date field in Graphical calculation view (in my case the field is DOJ).

 

7.ApplyIP.jpg

7A.GCV.jpg

 

Validate and Activate the view.

 

8.IPValueNotPassedFromProc.jpg

On Data Preview you see Input Parameter output value is not passed. Here comes the hurdle number 2

 

Somehow the output of procedure is not getting populated correctly. Could not catch the actual reason for this.

 

Generally when Attribute/Analytic/Calculation view is activated, a column view of the same is placed in _SYS_BIC from which the system access.

 

So I changed the schema name in procedure to _SYS_BIC.

 

(This is just my assumption and will validate once I have the information for why the procedure value is not returning.)

 

The only change this time will do is change the schema name to _SYS_BIC.

 

DROP PROCEDURE _SYS_BIC.FIRST_DAY;

CREATE PROCEDURE _SYS_BIC.FIRST_DAY (OUT FIRST_DAY VARCHAR(10))

LANGUAGE SQLSCRIPT

AS

 

BEGIN

SELECT TO_CHAR(ADD_DAYS(ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1),1)) INTO FIRST_DAYFROM DUMMY;

END

;

 

Check the output of your procedure by CALL _SYS_BIC.FIRST_DAY(?);

Now modify the Input parameter in calculation view to point to schema _SYS_BIC.

9.IPwithSysBic.jpg

Activate the view and do the data preview.

This time you will see the output of the procedure being populated. Hurdle number 2 crossed

10.ValueFromProc.jpg

 

Output of view based on input parameter value returned from procedure:

 

11.OutputWithDefaultValue.jpg

Getting the required result. Now again do the data preview and give the date we want,

12.GivenInput.jpg

 

Data is fetching as expected.

 

Thank you for reading

0 votes

One thought on “Input parameter based on procedure of type Date

  1. We need to give execute privilege on your schema in which procedure is created to _SYS_REPO

    GRANT EXECUTE ON SCHEMA RAJ TO _SYS_REPO WITH GRANT OPTION;

    By doing this, it is no more required to create procedure in _SYS_BIC.

    The above work around is no more required.

Comments are closed.