Skip to Content

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.

To report this post you need to login first.

29 Comments

You must be Logged on to comment or reply to a post.

  1. 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.

    (0) 
  2. 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

    (0) 
  3. 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

    (0) 
    1. Rich Heilman 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

      (0) 
      1. Sumin Dongol

        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

        (0) 
        1. Rich Heilman Post author

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

          Cheers,.

          Rich Heilman

          (0) 
          1. 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 );

            (0) 
  4. 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

    (0) 
    1. 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

      (0) 
    2. 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…

      (0) 
  5. 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.

    (0) 
  6. Nagababu Tubati

    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

    (0) 
    1. 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

      (0) 
      1. Rich Heilman 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

        (0) 
                1. Rich Heilman 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

                  (0) 
                      1. 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

                        (0) 
                            1. 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…

                              (0) 

Leave a Reply