SAP DBTech JDBC : Feature not supported : Scalar UDF does not support SQL Statement

Question

I'm new at HANA Studio, so please excuse me.

I am getting an error while creating function in SAP HANA Studio. Here's my function :

CREATE FUNCTION "PAYROLLDBTEST".GetAbsenteeismDays
(
-- Add the parameters for the function here
EmpID integer,
StartDate Datetime,
EndDate Datetime
)
RETURNS AbsDays float
LANGUAGE SQLSCRIPT   
SQL SECURITY INVOKER 
AS
AbsDays float;
BEGIN

 (SELECT SUM(DATEDIFF(DAY, "fromDate", "toDate") + 1) AS AbsentDays into AbsDays
                FROM HEM1
WHERE "empID" = :EmpID AND "fromDate" BETWEEN :StartDate AND :EndDate
                 AND "toDate" BETWEEN :StartDate AND :EndDate
                 );
RETURNS AbsDays;

error :

"Could not execute 'CREATE FUNCTION "PAYROLLDBTEST".GetAbsenteeismDays ( -- Add the parameters for the function here ...' in 2 ms 807 µs . SAP DBTech JDBC: [257] (at 576): sql syntax error: incorrect syntax near "RETURNS": line 23 col 1 (at pos 576) "

Answer 1

There are two types of functions in HANA: Table functions and scalar functions.

According to the SAP HANA SQL Script Reference (SPS 07) using DDL and DML within a table function is not allowed. Scalar functions do not support any kind of SQL-statements.

You return only a value and not a table, so you are trying to create a scalar function. Your mentioned example, executing a Select-statement inside a scalar function, won't work. But instead of creating a function you can also easily create a procedure containing the same functionality (if you really only need to get one float number).

CREATE PROCEDURE "PAYROLLDBTEST"."GetAbsenteeismDays" (
      in EmpID integer,
      in StartDate Datetime,
      in EndDate Datetime,
      out AbsentDays float )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER 
   -- DEFAULT SCHEMA <Schema>
   READS SQL DATA AS
BEGIN
   SELECT SUM(DATEDIFF(DAY, "fromDate", "toDate") + 1) into AbsentDays
      FROM HEM1
      WHERE "empID" = :EmpID
         AND "fromDate" BETWEEN :StartDate AND :EndDate
         AND "toDate" BETWEEN :StartDate AND :EndDate
END;