Skip to Content
Author's profile photo Rich Heilman

Scalar User Defined Functions in SAP HANA

Back in December, I introduced you Table UDFs in HANA 1.0 SPS5.  At that time, I also mentioned that we are working on implementing Scalar UDFs as well.   Today, I am very happy to announce that as of HANA 1.0 SPS6(Rev 60), we now support Scalar UDFs as well.  Scalar UDFs are user-defined functions which accept multiple input parameters and result exactly one scalar value.  These functions allow the developer to encapsulate complex algorithms into manageable, reusable code which can then be nested within the field list of a SELECT statement.  If you have worked with scalar UDFs with other databases, you know how powerful they can be.  Below is an example showing how to create two scalar UDFs, and then leveraging both within the field list of a SELECT statement.  This is a very simplistic example, and of course the logic can be done by other means, I just wanted to remove any complexity of logic and focus purely on the syntax.

CREATE FUNCTION add_surcharge(im_var1 decimal(15,2), im_var2 decimal(15,2))

RETURNS result decimal(15,2)

LANGUAGE SQLSCRIPT  

SQL SECURITY INVOKER AS

BEGIN

result := :im_var1 + :im_var2;

END

CREATE FUNCTION apply_discount(im_var1 decimal(15,2), im_var2 decimal(15,2)) 

RETURNS result decimal(15,2)

LANGUAGE SQLSCRIPT  

SQL SECURITY INVOKER AS

BEGIN

result := :im_var1 – ( :im_var1 * :im_var2 );

END;

Once you execute the CREATE statements in the SQL Console, the new objects will show up on the catalog in the “Functions” folder.

  /wp-content/uploads/2013/07/pic1_238480.png

As shown below, you can now use the functions in the field list of your SELECT statements.

  /wp-content/uploads/2013/07/pic2_238496.png

Again, this is a pretty simple example, but I think you can see how powerful a tool scalar UDFs could be to a developer.   Currently, both table and scalar UDFs can only be created via the SQL Console, but rest assured we are working to allow the creation of these artifacts in the HANA repository via an XS Project.

Assigned tags

      29 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Kumar Mayuresh
      Kumar Mayuresh

      Hi Rich

      Thanks for putting up an example and explaining scalar user defined function in SAP HANA SPS 06 

      Can you please provide some information by when SAP HANA Developer Edition on AWS will be updated to SAP HANA SPS 06 ?  

      Regards

      Kumar.

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      I have no idea when or if the developer center will update the Developer Editions. 

      Cheers,

      Rich Heilman

      Author's profile photo Ravindra Channe
      Ravindra Channe

      Hi Rich,

      Thanks for the informative blog. Can you please also comment on the performance impact on using such functionality. Is there one call to the UDF for each tuple or is there any performance optimization in the function call ?

      Regards,

      Ravi

      Author's profile photo Tim McConnell
      Tim McConnell

      Are there plans for string-based data type input parameters?  I would like to define a scalar UDF that takes in a single varchar argument but you currently received the following error: "Scalar UDF does not support the type as its input/output argument: Varchar3"

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Hi Tim. Yes using data types such as NVARCHAR or VARCHAR are currently not supported, but definitely should be in a future support package.

      Cheers,

      Rich Heilman

      Author's profile photo Former Member
      Former Member

      Understood. Thanks for a nice example Rich !

      Author's profile photo Former Member
      Former Member

      Hello Rich,

      Has the Scalar UDF been made available for the VARCHAR and NVARCHAR data types or any other character data types yet?

      Regards,

      Sumin Dongol

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      VARCHAR and NVARCHAR support in scalar UDFs is planned for SP8, scheduled to ship at the end of May.

      Cheers,.

      Rich Heilman

      Author's profile photo Former Member
      Former Member

      Thanks, Rich

      Cheers!!

      Author's profile photo John Appleby
      John Appleby

      I see this made it in, with a few other features:

      Various language features for Scalar User Defined Functions

      • Support string type(NVARCHAR, VARCHAR) input parameters

      • Support CASE/GREATEST/LEAST/COALESCE statements

      • Support Scalar User Defined Functions in assignment statement output_var := my_function( :input_var );

      Author's profile photo Ravindra Channe
      Ravindra Channe

      Hi Rich,

      I tried a simple example with the following code:

      CREATE FUNCTION  FN_R_CONC_COL (v_in integer)

      RETURNS result integer

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER AS

        v_temp integer;

      BEGIN

            Select "B" * "C" into v_temp from TT_2

            where "A" = :v_in;

            result := :v_temp;

      END;

      Table TT_2 contains 3 columns A, B and C with all of them defined as integer (Considering another limitation that Scalar UDF does not support VARCHAR as mentioned above).

      I get an error: SAP DBTech JDBC: [7]: feature not supported: Scalar UDF does not support SQL statements.

      If the above statement is true, then it is a great disadvantage and I don't see a practical usage of user defined scalar functions. Whatever limited functionalities possible within the function, can also be done in the select statement, which uses the scalar function. So, why such a feature is released which may not have a practical usage. Unless, I am completely wrong in understanding the concept of User defined Scalar Functions.

      Regards,

      Ravi

      Author's profile photo John Appleby
      John Appleby

      Ravindra - you're unfortunately right. There's no support for SQL functions, or even calling out to another SQLScript procedure, which would also be fine.

      I agree - Scalar UDFs as they currently stand do not have a use case that isn't solved using expressions and calculated columns.

      If I understand the way that HANA development works correctly - developers check in code and it is tested. Probably for SP06, the SQL functionality wasn't stable enough to be released to the public. So we have something which is currently not that useful, but will be upgraded in future builds. I hope that's soon!

      John

      Author's profile photo Henrique Pinto
      Henrique Pinto

      Nice finding, Ravi.

      Author's profile photo Peter Gao
      Peter Gao

      if you want calculator the values and want save time of each execute. HANA support the pre-calculator by pre-define column on table

      ALTER TABLE tt_2 ADD (bc INTEGER GENERATED ALWAYS AS b * c);

      then, directly get column bc inside SQL.

      one of pre-request is your tt_2 should be column table.

      and it always has some way to resolve the problem...

      Author's profile photo Adria Caireta
      Adria Caireta

      Hello,

      I have been waiting for the release of SAP HANA SPS 06 to be able to use scalar functions and my biggest disappointment is that

      SQL statements are not supported.

      Do you have any prevision of SQL statements being supported in a future release of SAP HANA?

      Thank you very much.

      Author's profile photo Former Member
      Former Member

      Hi Guys,

            UDFs are fine with VARCHAR as Input parameters...

      CREATE FUNCTION udf_alpha(IN v1 NVARCHAR(1))

      RETURNS TABLE (alpha VARCHAR(1))

      LANGUAGE SQLSCRIPT  AS

      BEGIN

      RETURN

        SELECT :v1 AS "ALPHA" FROM DUMMY

         UNION ALL

        SELECT :v1 AS "ALPHA" FROM DUMMY;

             

      END;

      SELECT *

             FROM udf_alpha('Z');

      correct me if I am wrong

      Regards

      Nagababu Tubati

      Author's profile photo John Appleby
      John Appleby

      Interesting. You are right. This wasn't supported in Rev.60 - wonder when it was sneaked in.

      By the way UDFs still do not support SQL - whilst your code appears to work, it is because it uses DUMMY and so the SQL is optimized out.

      John

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Nagababu's example is a table UDF where passing scalar nvarchar/varchar literal value as input parameters is and has always been supported.  Passing nvarchar/varchar parameters is still not supported for scalar UDFs.  Also, for table UDFs, of course SQL(SELECT statements) are supported, but NOT for scalar UDFs.  Again, the limitations are for scalar UDFs, not table UDFs.

      Hope it is clear.

      Cheers,

      Rich Heilman

      Author's profile photo John Appleby
      John Appleby

      Oops. Well spotted 🙂

      Hope this is fixed in SP07!

      Author's profile photo Jon-Paul Boyd
      Jon-Paul Boyd

      Thanks for the very useful blog Rich.

      Just taking a look at scalar UDF's, have SPS07 and it would appear SQL not supported as yet?

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      No, SQL is not yet supported inside scalar functions.

      Cheers,

      Rich Heilman

      Author's profile photo Former Member
      Former Member

      We are on SPS09, and still we do not see this feature. Can we expect this in near future?

      Thanks,

      Ganesh V

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Restrictions for SQL in the body of scalar UDFs is planned to be lifted in SP10 which is planned for release at the end of June.

      You will be allowed to do SELECT INTO statements, but you will get a warning that it is not recommended.

      Cheers,

      Rich

      Author's profile photo Former Member
      Former Member

      Thank you very much for the quick response. We would like to know if we can call stored procedures from the function in the upcoming SP10 release?.

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Yes, that is planned.

      Cheers,

      Rich Heilman

      Author's profile photo Eun Seok Bang
      Eun Seok Bang

      Hi Rich

      Just checked SPS 10 SQL reference but it seems like SQL statement is stil not supported for SCALAR UDF. would you confirm?

      Regards

      Eunseok

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Only SELECT INTO is allowed.

      Cheers,

      Rich

      Author's profile photo Eun Seok Bang
      Eun Seok Bang

      thank you for the confirmation.

      Regards

      EunSeok

      Author's profile photo Bernd Boehm
      Bernd Boehm

      I'm on SPS11 and I have an issue with my scalar function:

      FUNCTION "calculateAge" ( ID VARBINARY(32) )

      RETURNS Age VARBINARY(32) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

      BEGIN

         DECLARE currentDate DATE;

         DECLARE birthDate DATE;

      currentDate:=:=current_date;

      select "birthdate" into birthDate from "MyTable" where "bID" = :ID ;

      select (round(days_between(:birthDate ,:currentDate )/365)) into Age from dummy;

       

      END

      And I get an error like:

      SAP DBTech JDBC: [2048]: column store error: search table error:  [6900] Attribute engine failed;L execution failed with rc=20787, ...no data found exception: no data found,raw [here]eval_i(string unused_constant(), string unused_constant(), string unused_constant(), string unused_constant(), raw "SYSTEM.#_SYS_QO_COL_7f292a49a640:6000000000c496c.DWID"),SYSTEM.#_SYS_QO_COL_7f292a49a640:6000000000c496c.DWID = 'MIMICPRESCRIPTION_1026619'[raw]

      This seems very odd to me...