on 07-29-2013 10:56 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.