9/17/2019 Create Table Function in SQL using SAP HANA Studio

SAP ABAP Programming and HANA Database Tutorials Sign in | Join |

Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

HomeArticlesNewsIT JobsToolsSample ChaptersTrainersBlogsForumsPhotosFiles

Install SAP Free Create Table Function in SQL using SAP HANA Tweet

Studio SAP Tutorials

SAP Tutorial

SAP HANA database developers can create user-defined table functions which accept multiple input SAP Forums
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 SAP Tools
use table functions in JOIN statements with other HANA database tables in their SQLScript queries.
SAP Transaction Codes Table
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.
Meetup Sunumu 1

www.kodyaz.com/sap-abap/create-table-function-on-hana-database-using-sap-hana-studio.aspx 1/7
9/17/2019 Create Table Function in SQL using SAP HANA Studio

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"

www.kodyaz.com/sap-abap/create-table-function-on-hana-database-using-sap-hana-studio.aspx 2/7
9/17/2019 Create Table Function in SQL using SAP HANA Studio

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.

www.kodyaz.com/sap-abap/create-table-function-on-hana-database-using-sap-hana-studio.aspx 3/7
9/17/2019 Create Table Function in SQL using 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

www.kodyaz.com/sap-abap/create-table-function-on-hana-database-using-sap-hana-studio.aspx 4/7
9/17/2019 Create Table Function in SQL using SAP HANA Studio
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

www.kodyaz.com/sap-abap/create-table-function-on-hana-database-using-sap-hana-studio.aspx 5/7
9/17/2019 Create Table Function in SQL using SAP HANA Studio

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.

Online Sports Betting

10cric India: you find the best of sports betting
and casino games

www.kodyaz.com/sap-abap/create-table-function-on-hana-database-using-sap-hana-studio.aspx 6/7
9/17/2019 Create Table Function in SQL using SAP HANA Studio

Database Synchronization Create Dates Table CDS SQL Performance SELECT From Stored
Table Function using SAP Dashboards - Over 400 Procedure on SAP HANA
HANA AMDP Class performance metrics Database using SQLScript
Ad pervasync.com kodyaz.com Ad squaredup.com kodyaz.com

Backup, Archive, & Restore Create User-De ned Create Sample SQLScript Stock Aging SQL
- GRAX for CRM Function using SQLScript Cursor on SAP HANA Calculation Sample on SAP
on HANA Database Database HANA Database
Ad grax.io kodyaz.com kodyaz.com kodyaz.com

Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.

www.kodyaz.com/sap-abap/create-table-function-on-hana-database-using-sap-hana-studio.aspx 7/7