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.




CRM Companies List
Web Based CRM Software


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

sample SQL function for SAP HANA database developer

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.

SQL functions listed under SAP HANA 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;

SAP HANA SQLScript function sample code

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;





SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








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