All Forums Database
Prabhu_Teradata 17 posts Joined 06/12
11 Jul 2012
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

Thanks, Prabhu
Tags:
dnoeth 4628 posts Joined 11/04
11 Jul 2012

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

Dieter

Prabhu_Teradata 17 posts Joined 06/12
11 Jul 2012

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

                 

Thanks,
Prabhu

dnoeth 4628 posts Joined 11/04
11 Jul 2012

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

Dieter

Prabhu_Teradata 17 posts Joined 06/12
11 Jul 2012

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

Thanks,
Prabhu

24 Nov 2015

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

Fred 1096 posts Joined 08/04
24 Nov 2015

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.

24 Nov 2015

ok Thank you :)
 

You must sign in to leave a comment.