Create User-Defined Function using SQLScript on HANA Database
ABAP programmers working on SAP HANA database can create user-defined SQL functions using SQLScript like concatenating the first, middle and last name of an employee and return as a single string value representing the fullname. In this SQLScript tutorial, our sample SQL function will be fnGetFullname which will helps us to understand the basic steps of creating a HANA database inline function which can be used in the SELECT list of a SELECT query.
I have a demo HANA database table with first name, middle name and last name columns containing data for employees. In general the middle name column values are NULL or INITIAL for SAP applications. So, when ABAP programmer requires to get the fullname of a person, a string concatenation using SQL CONCAT function is used. Of course for more readility of the concatenated string, in our case the fullname, space characters are used to seperate each name fragment.
I'm sure SQL programmers will at first think that this is a very simple task to do.
But when you get into details you will face problems with NULL values, adding spaces, removing double spaces to single space, or trimming preceeding space or trailing space characters, etc.
Please try following SQL function on your SAP HANA database
CREATE FUNCTION fnGetFullname (
firstName NVARCHAR(40),
middleName NVARCHAR(40),
lastName NVARCHAR(40)
)
returns fullname NVARCHAR(100)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
fullname :=
TRIM(
BOTH ' ' FROM
CONCAT(
CONCAT(
IFNULL(CONCAT(firstName, ' '),'') ,
IFNULL(CONCAT(middleName,' '),'')
) ,
IFNULL(lastName,'')
)
);
END
This user-defined SQL function on SAP HANA database has been created using "create function" DDL command. Following the name of the SQLScript function name, the input parameters are defined between parentheses using parameter name and data type. After "RETURNS" the data type of the function output is identified. Our sample SQL function returns a string which is in nvarchar() data type with maximum length 100 characters. After "AS", between "BEGIN" and "END" statements, the SQL source code of the user-defined HANA database function takes place.
It is worth to note the use of TRIM() function with BOTH ' ' FROM argument, CONCAT() function and IFNULL() function in the code of the user-defined SQL function fnGetFullname.
After the above SQL function is created on the SAP HANA database, it will be possible to see it listed under the Functions node under your schema in the related catalog.
Now SQLScript programmers can use the SQL function just created to fetch the fullname of the employees, or customers and contacts, etc as seen in below SQL Select statement. I executed the below SQL Select script on SAP_HANA_DEMO schema. Since I created the user-defined SQLScript function under "KODYAZ" schema, I had to use the fully-qualified name of the SQL function object.
select top 10
"NAME.FIRST", "NAME.MIDDLE", "NAME.LAST",
"KODYAZ"."FNGETFULLNAME"("NAME.FIRST", "NAME.MIDDLE", "NAME.LAST") as FULLNAME
from employees;
Of course, it is a preferrable method to create public synonym object for the SQLScript function to make it easier for other database users to call the function without any authentication problem and ease of coding.
For more on creating public synonyms on SAP HANA database please refer to related tutorial.
create public synonym fnGetFullName for "KODYAZ"."FNGETFULLNAME";
After the public synonym object is created, HANA database programmer can build the SQL Select statement as follows which will produce the same output
select top 10
"NAME.FIRST", "NAME.MIDDLE", "NAME.LAST",
FNGETFULLNAME("NAME.FIRST", "NAME.MIDDLE", "NAME.LAST") as FULLNAME
from employees;
