SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




Install SAP Free


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..."

new HANA table function under repository package

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"

create 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.

HANA database SQLScript table function on SAP HANA Studio

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

save and activate SAP HANA Table Function

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

SAP HANA Table Function return data

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.






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems