[SOLVED] Why is it Not recommended feature: Using SELECT INTO in Scalar UDF

Why is it Not recommended feature: Using SELECT INTO in Scalar UDF

I have created a user-defined function using this sqlscript.

CREATE FUNCTION ZYESTERDAY ()
RETURNS YESTERDAY VARCHAR(8)
LANGUAGE SQLSCRIPT AS 
BEGIN
    SELECT TO_DATS(ADD_DAYS(CURRENT_DATE, -1)) INTO YESTERDAY
    FROM DUMMY;
END;

Although it worked fine but I got this warning message java.sql.SQLWarning: Not recommended feature: Using SELECT INTO in Scalar UDF.
I wonder which syntax I should use to avoid the warning message?
I tried to use YESTERDAY := SELECT TO_DATS(ADD_DAYS(CURRENT_DATE, -1)) FROM DUMMY;but an error message poped up like syntax error near select.

Solution

For your specific example this syntax seems much more straight-forward:

CREATE OR REPLACE FUNCTION ZYESTERDAY ()
RETURNS YESTERDAY VARCHAR(8)
LANGUAGE SQLSCRIPT AS 
BEGIN
    YESTERDAY = TO_DATS(ADD_DAYS(CURRENT_DATE, -1));
END;

This SAP Community Answer suggests that performance implications may be the reason for the warning. On my current SAP HANA Cloud system, I am not receiving this warning when creating the UDF using your syntax.