Getting error creating function

[Former Member]

Hello Experts,

I am trying to create the below function and getting error, I am creating this function to convert decimal to date

CREATE FUNCTION SDW_STAGE.ICOMS_DATE_CONVERSION
(  p_icoms_date  int)
RETURNS date LANGUAGE SQLSCRIPT AS

BEGIN
    IF p_icoms_date IS NULL THEN
        RETURN NULL;
    ELSE IF p_icoms_date IN (0,999999) THEN
        RETURN NULL;
    ELSE
        RETURN TO_DATE (p_icoms_date + 19000000, 'YYYY-MM-DD');
       END IF;      
       
END;

Please let me know what could be the issue.

Thanks,

Nishith

[former_member186082 · Accepted solution]

Hi Nishith,

Scalar UDFs are not yet supported, expected to work from SPS6, only table UDFs are supported. The below code works for your scenario. But you need to change logic for converting an integer into date format.

CREATE FUNCTION DATE_CONVERSION (IP_INT INTEGER) RETURNS TABLE (OP_DATE DATE)

LANGUAGE SQLSCRIPT AS

V1 INTEGER;

V2 DATE;

BEGIN

V1 := :IP_INT;

    IF :V1 IS NULL THEN V2 := NULL;

    ELSEIF (:V1>0 AND :V1>999999) THEN V2 := NULL;

    ELSE V2 := TO_DATE (:V1 + 19000000, 'YYYY-MM-DD');

    END IF;

    RETURN SELECT :V2 AS OP_DATE FROM DUMMY;

END;

Regards
Chandu

[Former Member · Accepted solution]

Thank you Chandra..

[former_member184768]

Hi Nishith,

I hope you are on SPS 06 to use Scalar UDF.

Please modify your code as mentioned below:

CREATE FUNCTION FN_DATE_CONV (  p_icoms_date  int)

RETURNS a date LANGUAGE SQLSCRIPT AS

BEGIN

     IF :p_icoms_date IS NULL THEN

         a := NULL;

     ELSEIF (:p_icoms_date = 0) OR (:p_icoms_date = 999999) THEN

         a:= NULL;

     ELSE

        a:= TO_DATE (:p_icoms_date + 19000000, 'YYYYMMDD');

       END IF;      

END;

select  FN_DATE_CONV(980601) from dummy;

this will work.

Regards,

Ravi

[Former Member]

Hi Ravi,

Thank you so much for the reply. When I try to execute your function it throws the error at BEGIN, not sure why? we are on SP05. 

[former_member184768]

It is time for you to upgrade to SPS 06.

Ravi

[Former Member]

Yes, We are planning to get it in short time. Thanks for your help..

[Former Member]

Hi Nishith,

You may need to change "ELSE IF" to "ELSEIF". please try again.

If occurs error, please post error screenshot and tell us which HANA server version is using SPS5 or SPS6 or others?

Hope this can help you.

Regards,

Jerry