All Forums Connectivity
scapuccini 2 posts Joined 10/08
07 Oct 2008
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?ThanksSimone

joedsilva 505 posts Joined 07/05
07 Oct 2008

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 Acan be re-written as ...SELECT A.*, B.DESCRFROM A, T00H12AL BWHERE B.COD_BK = A.PCodBkAND B.COD_FT= A.PCodFtAND B.COD_LAN=A.PCodLan ;

scapuccini 2 posts Joined 10/08
08 Oct 2008

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

Adeel Chaudhry 773 posts Joined 04/08
08 Oct 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.