Create UDF with SQL code

Connectivity

Create UDF with SQL code

Hi, i'd like to create the Teradata function corresponding to the DB2 function posted here:

CREATE FUNCTION SCLI.F00H12AL (PCODBK DEC(5), PCODFT VARCHAR(8),PCODLAN VARCHAR(2))
RETURNS VARCHAR(50)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT T00H12AL.DESCR
FROM T00H12AL
WHERE T00H12AL.COD_BK = PCodBk
AND T00H12AL.COD_FT= PCodFt
AND T00H12AL.COD_LAN=PCodLan

This function in very easy and it can be referenced in any SELECT clause of any SQL statement. I'd like to create the same function in Teradata but I still wasn't able to find the way yet. Reading the documentation I found out that UDF can reference to external file written in different programming language such as C/C++/Java, but my own case is much more simple. I only need to create a simple SQL language function.
Can anyone help?

Thanks

Simone
3 REPLIES

Re: Create UDF with SQL code


You can accomplish the same via normal joins without the necessity to have a UDF.

SELECT A.*, SCLI.F00H12AL(A.PCodBk, A.PCODFT , A.PCODLAN )
FROM A

can be re-written as ...

SELECT A.*, B.DESCR
FROM A, T00H12AL B
WHERE B.COD_BK = A.PCodBk
AND B.COD_FT= A.PCodFt
AND B.COD_LAN=A.PCodLan
;

Re: Create UDF with SQL code

Thank you very much for the quick answer, but my issue was to find out if it's possible in Teradata to write a UDF with embedded SQL (like the one in the posted example) or UDFs only need to access an external C/C++ source file. This because in our company we're doing a migration of some applications from DB2 to Teradata and we need to rewrite all DDL for this DBMS.

Thanks
Simone
Teradata Employee

Re: Create UDF with SQL code

Hello,

Till Teradata 12 SQL can not be used in user defined functions. Perhaps, it will be available in later versions.

Regards,

Adeel