Skip to Content
Technical Articles
Author's profile photo suman kolatum

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

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.

 

 

 

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo shaik ali
      shaik ali

      Nicely explained, thanks you.