Passing Stored Procedure out parameter value to Calculation View's Input Parameter

[ksuman2000]

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

SNO	SNAME	SCLASS
1	SUMAN	SIXTH STANDARD
2	RAJ	SECOND STANDARD
3	GIRI	FOURTH STANDARD
4	DAVID	SECOND STANDARD
5	FAROOQ	FIFTH STANDARD

Table2 - ZJOIN_DETAILS

SNO	JOIN_DATE	AGE
1	2019.04.09	16
2	2019.04.29	7
3	2019.05.03	9
4	2019.05.13	7
5	2019.05.17	10

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.

 

 

 

[shaik_ali]

Nicely explained, thanks you.