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;

Answer 2

The downside of @hoffman's solution is that you can't use a PROCEDURE in a projection (like a WHERE-part of a query):

SELECT * FROM myTable WHERE "myProcedure"('param') > 5;

gives

cannot use procedure or table function in select projection column

From HANA 11 on scalar functions do support SQL so there is no need to use PROCEDUREs here any more (see https://scn.sap.com/thread/3861611)

happy