﻿ SAP HANA Database User-Defined SQL Factorial Function
 SAP ABAP Programming and HANA Database Tutorials Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

# SAP HANA Database User-Defined SQL Factorial Function

In this SAP HANA database SQLScript tutorial, I want to share source codes of HANA factorial function. Using SQLScript, SAP HANA database developers can create user-defined scalar functions in order to use in inline expressions and make frequent calculations or tasks in SQL by using these HANA functions. Though table valued functions return tabular data, scalar function return single value in different HANA database data types.

As the definition of mathematical factorial function is taking a positive integer value and returning the multiplication of all integers from 1 to input parameter value, my SAP HANA factorial function accepts an integer parameter as input.

The output of the HANA factorial function is also integer.

If SQLScript developers review following code, they will see an IF clause checking whether the input integer value is bigger than or equal to 0.
If the input value is zero, output is 1. From maths the value of 0! equals to 1.

Within the IF clause, a WHILE loop takes place to calculate the factorial of the given input integer parameter.

To test our new SQL factorial function on a SAP HANA database, developers can execute following SQL query

The results are as expected as seen in below screenshot

I hope this HANA database SQLScript function example will help you quickly solve your development requirements and is a good example of IF clause and WHILE clause usage within UDF functions on HANA database.

Besides above given SQLScript WHILE Loop, HANA database developers can use SERIES_GENERATE_INTEGER function with dynamic execution command EXECUTE IMMEDIATE as follows:

HANA database SQLScript programmers can wrap above SQL code block into a stored procedure as follows:

To execute above SQL procedure following CALL procedure command can be used as a sample

I tried to return the result of factorial calculation into an integer variable that is defined as an output parameter of the GetFactorialOf procedure, but there is a limitation related with INTO clause in SELECT statements of "EXECUTE IMMEDIATE"

SAP DBTech JDBC: [337]: INTO clause not allowed for this SELECT statement

So I preferred to return factorial information as a result set

Unfortunately, there are some limitations preventing to convert above SQL code into HANA database scalar function. One of the errors that you might experience with use of dynamic SQL code in a scalar function is seen below:

SAP DBTech JDBC: [7]: feature not supported: Dynamic SQL is/are not supported in scalar function

SAP HANA and ABAP