SAP HANA Table Functions

Welcome to the SAP HANA Table Functions tutorial. SAP HANA Table functions replace Scripted Calculation views as the recommended development artifact whenever the graphical views are not enough to implement a technical requirement.

Let’s start off with a simple scenario to build a table function that provides the Client(field MANDT) and Sales Document(field VBELN) of the table VBAK.

To start, as always, make sure you are in the development perspective.

A table function is a development artifact and must be created in the repositories tab like the HDB tables we created in an earlier tutorial.

Switch to the “Repositories” tab.

Under the package, you would find the views that were created throughout the tutorial and also the other development artifacts created under the repositories tab earlier.

Right click on your package and select New Other

This brings up the below “Wizard” window. Here, it is needed to select the object you wish to create. The easiest and fastest way to do this is to search for it by name in the area marked below.

As you begin to type in “table”, the objects matching this string start to appear below.

Select “Table Function” because that is what we wish to create. Press Next once selected.

Now provide a name to this table function. The name sales_tab_function has been entered as below. The extension .hdbtablefunction gets auto-appended once you click anywhere outside this text box.

Press Finish.

This opens up a table function with some default code already present and makes up the skeleton of the overall code which is always needed.

Firstly, let’s clean up the comments placed by the system. Remove them by deleting the text between BEGIN and END block.

Now the below code looks cleaner. Remember, this was all auto-generated.

We have not written anything by ourselves yet.

Let’s understand these rows one by one. The FUNCTION keyword marks the beginning of a SAP HANA function. This keyword is followed by the auto-generated complete name of the table function which is a combination of the username we used to create this table function, the package path in which this function was created along with the name we gave it while creating it.

The set of open and closed brackets at the end is used to add any Input parameters to this table function. For now, we will leave it blank inside.

The next line starts with a RETURNS keyword. The return_table_type is just a placeholder for the actual code. This is similar to the columns folder in the Scripted view where you specify the list of output fields for the Scripted node.

We need to remove this return_table_type and provide the structure and datatype of the output we expect from this table function.

As seen below, we add the keyword TABLE to specify that the output of this function is a table (hence the name table function) and it will have 2 fields

• CLIENT of data type NVARCHAR and length 3
• SALES_DOC of data type NVARCHAR and length 10

LANGUAGE SQLSCRIPT tells the HANA system that SAP HANA SQL Script would be the language used to code the logic for this table function. Developers can also use the analytical language ‘R’ instead. But this is rarely used.

1

There are 2 security modes:

1. INVOKER: In this case, the function executes with the privileges of the invoker of the procedure.
2. DEFINER: In this case, the function executes with the privileges of the definer of the procedure.

It’s always best to leave it as INVOKER so that whenever this function is called, it will run based on the invoking user’s privileges.

The BEGIN and END mark the start and end of the block in which we need to write our code. Don’t forget the humble semi-colon at the end.

Start off by writing RETURN keyword just below begin. This tells HANA that the select statement you write below this would be the output of this table function.

You might also notice that there is a red cross mark next to the END keyword now. This is because there is a RETURN keyword but no logic below it returning any data yet. Don’t worry. It will be fixed as soon as we finish our code.

Now, we are required to write the select statement which provides output in the same structure and order as the RETURNS TABLE statement in line 2.

Our requirement is quite simple here in this example where we select field MANDT and VBELN from table VBAK and rename them using field alias concept <link alias tutorial here>.

You can also return complicated SQL queries with JOINs and UNIONs or any other operations here as output as per your requirement.

Once done, press the activate button marked by the arrow below to activate your development in the HANA repository.

As marked by the arrow below, you can now notice that an active table function is now available.

This SAP HANA Table functions tutorial is continued on the next page.

<<Previous Tutorial                                                                                                                    Next Page>>