SAP HANA Table Function linking with Calculation view Input Parameters

This tutorial is a continuation of the SAP HANA Table Function Part-1 tutorial and Part-2 tutorial. Please read them first if you have directly landed on this page.

SAP HANA Table function (Contd..)

To understand how input parameters work with an SAP HANA table function, we continue with the same example we used earlier. Go to the repositories tab again and double click on our table function to edit it.

SAP HANA Table function

Let’s say the new requirement is to have an input parameter take the value of a sales document from the user and this sales document number should be used as a filter in our select statement.

To start off, as explained earlier, in Line 1 of the code, the round brackets should enclose the input parameter(s) we require in the code. In this case, we create one called p_sales_doc of datatype NVARCHAR and length 10 as shown below.

SAP HANA Table function



Also, modify the SELECT statement to include the WHERE condition as shown below. Here we tell HANA that we need only values where the value of sales document (VBELN field of table VBAK) matches the value specified by the input parameter p_sales_doc.

Note: As explained earlier in the scripted calculation view tutorial, input parameters are used in SQL codes with a colon (:) suffix.

Activate the table function again using the Activate button marked below.

SAP HANA Table function

Once this is done, switch back to the Systems tab and open the Calculation view we created for this table function in the previous tutorial.

SAP HANA Table function



1

Click on the projection tab to open the fields and other properties.

The calculation view cannot automatically detect an input parameter coming in from the table function. Therefore, we need to create an input parameter for this calculation view with the same data type and map the input parameter from the table function to the one of this calculation view.

To start off, right click on the input parameters folder and select “New”.

SAP HANA Table function

In the window that opens up, then we provide the same name and data type as the table function parameter.

Note: It is not mandatory to have the same name. It was just kept the same here for consistency. Data types and lengths should ideally be compatible and preferably same.

Press OK when done.

SAP HANA Table function

We now see that the input parameter has been successfully created.

SAP HANA Table function

The next step is to establish a connection between the table function’s input parameter and the calculation view’s input parameter.

To do this, right click on Input parameters and click on “Manage Mappings”.

SAP HANA Table function

On the left side, you would see the input parameter from the table function and on the right hand side the calculation view input parameter would be present as shown below.

Drag the one on the left and drop it on the right one to create a mapping.
SAP HANA Table function

Once you drag and drop it, the mapping would get established as illustrated below.

Press Ok to confirm the mapping.

SAP HANA Table function

Even after the mapping, you would see no difference in the view properties though.

Activate this view now.

SAP HANA Table function

Execute a data preview. This would bring up the pop-up asking for a value for the sales document number.

SAP HANA Table function

A sales document number that exists in this table is entered as an example. Press Ok.

SAP HANA Table function

As seen below, the filter was passed on to the logic of table function and it got applied successfully. Only the row with the selected sales document was sent to the output.

SAP HANA Table function

This confirms that our table function and the input parameter mapping was a success.

Help this website grow by sharing this document on social media by using the icons below. Be sure to subscribe to our newsletter when the message pops up for latest alerts on new tutorials.

<<Previous Page                                                                                            Next Tutorial>>

Tagged .

13 Comments

  1. Hi, this was an awesome and simple explanation of table functions, thanks for that, can you pls let us know about procedures in Hana , as I can see now no procedure needs to be created as hdbbprocedure only, old procedure creation was throwing error for me on hcp.

    Thanks in advance.

    Manue

  2. Hi,It was a nice blog and nicely explained.In the example that you have shown, when we are not passing any value in the input parameter, it does not return any data.How to handle such scenarios.

  3. Hi,

    Thanks for the detail explanation.
    Is there a way where we can do auto/dynamic mapping of the schema where we are creating this table function rather than hard coding it to “SHYAM” ( as i see in your screen shots).
    As we transport these table functions to higher environments, it is not advised to have this under our schema rather than in public schema’s like DEV in Development environment or PRD in production environment.

  4. Hi Shyamuthaman,

    I have a question. In your demo, the input parameter value is single. If the input parameter is multi-value, how does the mapping work? How does the table function take in multi-value input parameter? Thanks.

  5. Hi Shyamuthaman

    Can you tell me why we should go for table function because we have Graphical Calculation View and HDB Procedures.

Leave a Reply

Your email address will not be published. Required fields are marked *