Table Functions in SAP HANA

[former_member181964]

In this article I'm going explain how to create SAP HANA Calculation View using Table Function. In my previous article SAP HANA Calculation View - SQL Script, I explained by using SQL Script with SQL Scripted Calculation View, but in current article I'm going to use the same SQL code using in "Table Function".

In SAP HANA, there are multiple ways to provide a solution for a given business requirement. Choosing Method-1 or Method-2 or Method-n depends on various parameters/situations. It is not like using Scripted Views are old method and using Table Functions are new method. In below example, I used the same code (from my previous article), it is working in the below Calculation view. But it is always good to choose the best method based on requirements/situation.

We are getting new things from version to version in SAP HANA DB, because SAP is doing lot of R&D and technology is evolving.

Lets start our journey with User-Defined Functions (UDF), and in this article I'm going to explain/use Table User-Defined Functions (UDF).

In SQL, we have Scalar and Table User-Defined Functions (UDF), User Defined Function (UDF) enables you to build complex logic into a single database object.

Scalar user-defined function (UDF) is a custom function that will returns only one scalar value as output.

Table user-defined function (UDF) is a custom function that will returns only one Tabular value as output.

Step 1: Create a table type which is required to display OUTPUT (it may be from one table or from multiple tables).

In SAP HANA Calculation View - SQL Script, I used five tables, here also I'm going to use the same five tables and same OUTPUT fields.

Use the below SQL Code and create a Table Type.

See the table type once the above code is executed successfully.

Step 2: Use SAP HANA Development Perspective, create a Work Space in Repositories and create XS Project in Project Explorer and assign Work Space.

Then go to Repositories, Create a Repository Package, or if you have already then choose your repository package and create new Table Function and give File name "GET_SALESORDERS_TF"

The below Function will creates with template (code).

Step 3: We need to do simple modifications to above code and keep our code here.

Activate.

Step 4: Create a Calculated View in SAP HANA Modeler Perspective and call/assign the above Table Function. Once we add the Table Function, then it will show all fields which are available in Function (Table Type).

Add all fields in Projection-->Aggregation-->Semantics.

Save & Activate.

Step 5: Display Data.

I conclude that, we need to choose either Scripted Calculation views or Table Function based Calculation views depending on situation.