Creating SQL UDF issues

Extensibility
Enthusiast

Creating SQL UDF issues

I am trying to create a UDF SQL function in Teradata 13, but keeping getting errors when I try to compile it. So I have tried creating one from the Teradata documentation just to see if I could get it to work, but and getting the same error message.

CREATE FUNCTION NS_TEST_DB.MyUDF (a INT, b INT, c INT)
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN a + b - c;

ERROR: CREATE FUNCTION Failed. 3707: Syntax error, expected something like a name or Unicode delimited identifier between teh 'LANGUAGE' keyword and the'SQL' keyword.

The function I would like to get to work is as follows, but am getting same error as listed above

CREATE FUNCTION NS_TEST_DB.UDF_REMOVE_ROUTE_NUMERICS (ip_route VARCHAR(90))
RETURNS VARCHAR(90)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN
DECLARE sub_str VARCHAR(90);

SET sub_str = TRIM( ip_route);

REPEAT
IF SUBSTRING(sub_str FROM CHARACTER_LENGTH(sub_str) FOR 1) IN ('0','1','2','3','4','5','6','7','8','9')
THEN SET sub_str = TRIM(SUBSTRING(sub_str FROM 1 FOR CHARACTER_LENGTH(sub_str) -1));
ELSE SET sub_str = TRIM(sub_str);
END IF;

UNTIL SUBSTRING(sub_str FROM CHARACTER_LENGTH(sub_str) FOR 1) NOT IN ('0','1','2','3','4','5','6','7','8','9')
END REPEAT;

END;

Any input would be very helpful.
2 REPLIES
Senior Apprentice

Re: Creating SQL UDF issues

The first example runs without errors.
SQL UDFs are available in TD13.10, seems you try to run it on TD13.

But as this is the first release supporting SQL UDFs they're still limited, you can't do DECLARE/SET/REPEAT.
You need a monster case statement:
return
case
when position(substring(ip_route from 90 for 1) in '0123456789') = 0 then substring(ip_route from 1 for 90)
when position(substring(ip_route from 89 for 1) in '0123456789') = 0 then substring(ip_route from 1 for 89)
when position(substring(ip_route from 88 for 1) in '0123456789') = 0 then substring(ip_route from 1 for 88)
....
end

If you installed the Oracle UDFs you can achieve the same result using otranslate:
substring(ip_route from 1 for char_length(trim(trailing '0' from otranslate(ip_route, '0123456789','0000000000'))))

Dieter
Enthusiast

Re: Creating SQL UDF issues

Thanks..

We are on 13.0 here.

I was afraid about not being able to use the declare, and repeat clauses. I have made a request to our DBA group to install the Oracle functions, so hopefully that will get me what I need.