SQL user defined function in Teradata

Database
Enthusiast

SQL user defined function in Teradata

Hi,

         Am having a oracle sql with oracle UDF function in it.. the udl function is getting input value and performing some manipulation based on the input value in a table data and returing the output. I want to convert it into teradata.

For EG:

 Select table1.column1, user_defined_function(table1.column2, table1.column3 ) from table1

where.... <condition>

where as the function  user_defined_function(parm1, parm2) will get the input and perform some SQL manipulation and retun the value

Please help me to convert. It's urgent

Thanks,

Prabhu

Tags (1)
7 REPLIES
Junior Contributor

Re: SQL user defined function in Teradata

Hi Prabhu,

depending on your Teradata release and the actual code of the UDF you might do exactly the same in TD, but it's hard to say without any details.

Dieter

Enthusiast

Re: SQL user defined function in Teradata

Hi Dieter,

Teradata Version: 12

       Oracle function is taking the input value (parm1 and parm2) and returning 3 values a,b,c

for e.g.

 select a, b, c  from

      (select case when a1 > 0 then a1 else a2 as a

                  h+f as  e , j/e*a*100 as b,  j+(n/b)*h as m , m*m as c from tablexyz

where h=parm1 and j = parm2 ) k

inner join ......

and the function is returning a b c

Thanks,

Prabhu

Junior Contributor

Re: SQL user defined function in Teradata

Hi Prabhu,

a SQL UDF in TD13.10 can't return multiple columns.

You have to rewrite it using an Outer Join:

select t1.columns,

  t2.a, t2.b, t2.c

from table1 t1

left join

  (select h, j,

     case when a1 > 0 then a1 else a2 as a

     h+f as  e , j/e*a*100 as b,  j+(n/b)*h as m , m*m as c from tablexyz

  ) as t2

on t2.h= t1.column2 and t2.j = t1.column3

inner join ......

If this UDF is referenced in different places you should create a view and use it instead.

Dieter

Enthusiast

Re: SQL user defined function in Teradata

Thanks a lot Dieter, Please help me with the post

http://forums.teradata.com/forum/general/how-to-execute-macro-and-stored-procedure-in-xml

Thanks

Prabhu

Re: SQL user defined function in Teradata

Hi,

I have two clarification points regarding the Teradata SQL UDF.

a) Can we use a direct select query to get data from multiple tables in the Teradata SQL UDF(without external program /embedded sql)?

b) If the above is not possible, Can we invoke a stored procedure from a teradata SQL udf? the stored procedure would do the required select query from the tables?

Below is the requirement for the same:

We need to get two dates as input variables to a Teradata UDF and compute the number of business days between the same. The function should be generic and be usable in a SQL select query used either directly or through a third party tool(ETL, Reporting etc).

Any assistance would be greatly appreciated.

Thanks,

Vivek

Teradata Employee

Re: SQL user defined function in Teradata

Please start a new topic for a new question.

In both cases, the answer is no. A SQL UDF definition must be a SQL expression, and NOT a SQL statement; it may not contain even a scalar subquery.

Re: SQL user defined function in Teradata

ok Thank you :)