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)
9 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 :)

Teradata Employee

Re: SQL user defined function in Teradata

Hi doneth,

I am creating one function and which if that is executed It is returning me sql stmt.I don't want it to return the sql built.I wanted it to execute the SQL and return eith "A" or "B" this should be a simple function.Not sure why it is difficult for me to see what the issues are when you execute the function the return value is the full SQL statement NOT the Identifier of  A or B.  I need it as a function so it can be embedded into views and sql. Kindly help me out with it.

CREATE FUNCTION sysdba.ChckEXT()
RETURNS VARCHAR(10)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 'SELECT CASE when 1 = 1 then "A" else "B" end from dbc.dbase
where user= databasename and ownername = "EXT_ard"';

 

 

Teradata Employee

Re: SQL user defined function in Teradata

As @Fred said, just 3 lines above your post, "A SQL UDF definition must be a SQL expression, and NOT a SQL statement; it may not contain even a scalar subquery."  So, in particular, you cannot do a Select in a SQL UDF, or any other UDF for that matter.  This is the kind of function that you must translate to a join to a view.  For a complete explanation with examples, see this blog post.

 

I'm not sure what you're trying to do in this case, but instead of "Select ..., ChectExt(user), ..." you might say,
   Select ..., case when CX.val is null then 'B' else 'A', ...

   from ...,

   left outer join <view-name> CX on user=CX.databasename

   ...

 

and the <view-name> would contain your select statement, "SELECT databasename, CASE when 1 = 1 then 'A' else 'B' end as val from dbc.dbase where ownername = 'EXT_ard'."  Or, instead of a view you could make this a derived table or define it in a with-clause.  Like I said, I'm not sure what you're checking here but this is the basic idea.  See my blog for more ideas.