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