Passing Stored Procedure out parameter value to Calculation View’s Input Parameter
Description: In this article, we are going to learn how to use stored procedure to generate the value for the input parameter of calculation view dynamically.
Let’s take the example of retrieving latest one week student joining details from below tables.
Table1 – ZSTUDENT
Table2 – ZJOIN_DETAILS
To achive this first create a stored procedure in Repositories tab of SAP HANA Development prospective to retrieve the latest joining date of students from ZJOIN_DETAILS table.
Run the simple test for above stored procedure in SQL console
From the above test we can say it is successfully returning the latest joining date from ZJOIN_DETAILS database table.
Now create the calculation view RECENT_JOINERS with input parameter in Systems tab of SAP HANA Developement prospective to utilize the above stored procedure.
select the projection and add object ZJOIN_DETAILS and add all fields to output
now create the input field IP_RECENT_DATE as below
now create a calculated column NEW_DATE with DATE data type from input parameter IP_RECENT_DATE.
Now create a filter on JOIN_DATE field of Projection_1 from right clicking on Expression under Filters folder
select the projection_2 and add object ZSTUDENT and select below fields to output
Select join for joining Projection_1 and Projection_2 with field SNO and select the below fields for output
now map the Join_1 fields to Aggregation and select all fields to output
now save and activate the view then click on Data Preview
Under Raw Data tab we can see the latest one week joining details of students with the latest joining data that has been received from input parameter passed from stored procedure in calculation view.
Conclusion: From the above example we learnt how to use the stored procedure to pass the input value to input parameter of the calculation view in HANA . So that we can restrict the selection from the projection object at very start level.
Nicely explained, thanks you.