Create a function in SAP HANA

[former_member212706]

Dear Experts,

I am trying to create a sample function in SAP HANA . I am facing a problem while creating it.

It throws an error stating "

Could not execute 'CREATE FUNCTION GS_TEST (IP DECIMAL,OP DECIMAL) RETURNS DECIMAL LANGUAGE SQLSCRIPT AS v1 DECIMAL; ...' in 285 ms 343 µs . SAP DBTech JDBC: [257] (at 430): sql syntax error: incorrect syntax near ";": line 22 col 1 (at pos 430)

please help me here.

TIA

Regards,

Rajesh.

[former_member184768 · Accepted solution]

Hi Rajesh,

I am not completely sure if currently scalar type is implemented (thought it is mentioned in the documentation) as return data type. Can you please change it to table type like RETURNS table (a DECIMAL) and the return statement as

return select :v2 as a from dummy;

and check if it works.

Regards,

Ravi

[former_member212706 · Accepted solution]

Dear Ravi,

Thanks a lot for the response. I tried it as well it doesnt work.

CREATE FUNCTION "INF182285"."GS_TEST2" (IP DECIMAL,OP DECIMAL) RETURNS table (a DECIMAL)

LANGUAGE SQLSCRIPT AS

BEGIN

RETURN select 1.1 as a from dummy;

END;

This works. But when i add the code it causes problem.

CREATE FUNCTION "INF182285"."GS_TEST1" (IP DECIMAL,OP DECIMAL) RETURNS table (a DECIMAL)

LANGUAGE SQLSCRIPT AS

v1 DECIMAL;

v2 DECIMAL;

BEGIN

v2 := :IP;

v1 := :IP-:OP+0.6;

IF (v1 <> 0) THEN

  IF (v1 > 0) THEN

    WHILE (v1 <= 0) DO

      v2 := v2-0.0001;

      v1 := v2-:OP+0.6;

     END WHILE;

   ELSE IF (v1 < 0) THEN

    WHILE (v1 >= 0) DO

      v2 := v2+0.0001;

      v1 := v2-:OP-0.6;

     END WHILE;

  END IF;

END IF;

RETURN select v2 as a from dummy;

END;

The error still persists.

Thanks,

Rajesh.

[former_member184768 · Accepted solution]

Please try the following:

CREATE FUNCTION FN_T1 (IP DECIMAL,OP DECIMAL) RETURNS table (a DECIMAL)

LANGUAGE SQLSCRIPT AS

v1 DECIMAL;

v2 DECIMAL;

BEGIN

v2 := :IP;

v1 := :IP-:OP+0.6;

IF (:v1 <> 0) THEN

  IF (:v1 > 0) THEN

    WHILE (:v1 <= 0) DO

      v2 := :v2-0.0001;

      v1 := :v2-:OP+0.6;

     END WHILE;

   ELSEIF (:v1 < 0) THEN

    WHILE (:v1 >= 0) DO

      v2 := :v2+0.0001;

      v1 := :v2-:OP-0.6;

     END WHILE;

  END IF;

END IF;

RETURN select :v2 as a from dummy;

END;

[Jonathan_Haun · Accepted solution]

I'm not able to create a scalar type function either:

CREATE FUNCTION SimpleMath (var INTEGER)

RETURNS INTEGER LANGUAGE SQLSCRIPT AS

V_y INTEGER;

BEGIN

V_y := :var * 10;

RETURN :V_y;

END;

[lbreddemann · Accepted solution]

Hi Ravi,

the documentation is wrong here - scalar user defined functions are currently not supported.

I files a bug report for this.

- Lars

[Jonathan_Haun · Accepted solution]

Lars,

Where do we make a request for adding this feature to SAP HANA? There is not a category for SAP HANA on https://ideaplace.brightidea.com/. I am working on developing OLTP applications on SAP HANA and the XS engine but found that not having scalar type UDFs was a bit limiting. It would be nice to have them for the ROW store tables at a minimum. I am sure there are some reservations for implementing them on columnar store tables but I don't see why this would be an issue for the ROW store tables.

Thanks,

Jonathan Haun

[lbreddemann · Accepted solution]

Hi Jonathan,

I haven't said that scalar UDF are not going to be implemented, did I?

The idea and requirement are clearly understood by HANA development management.

But if, when and how this feature is going to be implemented and released to customers is not something I can comment on.

- Lars

[Jonathan_Haun · Accepted solution]

I'm Sorry Lars. I did not imply that they were not going to be implemented. However, at the moment they are not available in SPS5. I was simply looking for a way to communicate the need to the product team and also get some indication as to when they will be available. If they already know its a need, that is great news.

Thanks for the comment.

[RichHeilman · Accepted solution]

All,  scalar UDFs are planned for HANA 1.0 SP6.

Cheers,

Rich Heilman

HANA Product Management