Create Table Function in SQL using SAP HANA Studio
SAP HANA database developers can create user-defined table functions which accept multiple input parameters and returns a table as output of the function execution. User-defined table functions aka Table UDF SQL functions can be used in the FROM clause of another SQL query, which enables database developers to use table functions in JOIN statements with other HANA database tables in their SQLScript queries.
In this HANA database tutorial for SQL developers, I'ld like to show how programmers can create table functions using SAP HANA Studio and use these table functions in their SQL queries with a basic sample case.
Launch SAP HANA Studio.
On Repositories tab, connect to target SAP HANA system.
Drill down the Repository Package you want to create the table function under.
Right click on the repository package, follow menu option "New > Other..."
When wizard is displayed, either stary typing "table function" so that it will be listed under existing wizards to create SAP HANA database development objects, or drill down following path "> SAP HANA > Database Development > Table Function"
When Table Function is listed, highlight and select it, then press Next to continue with the next step in the table function creation wizard.
If you have selected the correct repository package, you can just type the desired table function name in "File Name" textbox. It will automatically complete the file name by adding ".hdbtablefunction" at the end of the function name you provided.
Press Finish button.
The SAP HANA Table Function Editor opens with create template as follows modified with provided information by SQL developer.
FUNCTION "A00019719"."com.kodyaz.som.sales::tf_invoice_list_table_function" ( )
RETURNS return_table_type
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
/*****************************
Write your function logic
*****************************/
END;
What can a SQL developer do to modify this table function is providing input parameters and defining the return table type in detail.
As a second step, programmer should type required SQLScript codes to populate returning table by using input parameters which can be summarized as function logic.
Following is the final version of this tutorial's sample table function source code.
There are two input parameters used to identify the minimum and maximum invoice numbers that will be returned by the table function.
FUNCTION "A00019719"."com.kodyaz.som.sales::tf_invoice_list_table_function" (
VBELN_min varchar(10),
VBELN_max varchar(10)
)
RETURNS TABLE (
vbeln varchar(10),
erdat varchar(8),
netwr dec(15,2)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
RETURN
select
vbeln, erdat, netwr
from "SAPABAP1"."VBRK"
where
vbeln between :VBELN_min and :VBELN_max;
END;
Developers can realize, input parameters (one or more) are provided right after table function name.
And return table type or returning column list is defined with column names and data types right after "RETURNS TABLE"
The logic managed by SQLScript codes should return the table data by using RETURN command.
IF you have experience on CDS Views with parameters, you already know how to use input parameters in SQLScript code or in a SQL query. All you need to do is using ":" in front of the parameter name to refer to its value.
Save the table function .hdbtablefunction code either by pressing SAVE icon or press Ctrl+S
Then Activate the SAP HANA table function using Activate icon or press Ctrl+F3
SQL programmers can Execute following SQL SELECT statement using the Table Function in the FROM clause.
select * from "A00019719"."com.kodyaz.som.sales::tf_invoice_list_table_function"('0090000003', '0090000009')
Here is what returns with table function showing how it can be used
Troubleshooting authorization problems: If you experience an error similar to following one, please refer to article Create Your First SAP HANA Calculation View where you can find a HANA database GRANT SELECT ON SCHEMA SQL command in action at the end of the tutorial.
Could not execute ...
SAP DBTech JDBC: [258]: insufficient privilege: Not authorized
The error is probably as a result of _SYS_REPO system user's missing authorization on specific database objects.
I can suggest database developers to read Create SQL Numbers Table for SAP HANA Database tutorial which can be useful for many cases for programmers.
