write SQL UDF in Teradata

Database

write SQL UDF in Teradata

Hi,

I want to write SQL UDF with Multiple Lines:

1. Declare variables.

2. Set those variables with select into statement.

3. some if conditions on Variables

4. return some value.

What is the syntax to write this.

I'm using teradata Version 14.00.03.02.

Parth

11 REPLIES

Re: write SQL UDF in Teradata

I think it is better for your case to write in java or c or c++.

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1147_111A/ch04..., even examples are there

Few cases I write in SQL. But many I write in Java, because I can reuse in MR jobs in hadoop  and few in c,c++. I always run on Linux environment.

Re: write SQL UDF in Teradata

but i want to read data from database tables.

is this possible. if yes, will it hurt the performance?

Re: write SQL UDF in Teradata

That is what they are made of. Love unix and C programming too but I choose java here.For example, there are few udfs I made in java, say like search for a pattern. Compile that java code. Create a function of that java code in Teradata, then use it, similar to the examples above.

So far I have not seen a performance issue. Even if there is a performance issue, you can tweak somehow.

Just to share with you,I have seen people scream about performance issue. It depends how we deal with it. There are many  techniques. Only when we bump into issues and we solve them , we remember and find out ways and means.

N/A

Re: write SQL UDF in Teradata

SQL UDFs are "only" wrapper to encapsulate some SQL code to make code reusable and more readable.

What you descibe sounds much more like a Stored Proceedure

Re: write SQL UDF in Teradata

thanks all

Re: write SQL UDF in Teradata

Hi Parth,

Can you please share how you resolved your SQL UDF problem.  My requirements are also similar.

Regards,

Amit.

Teradata Employee

Re: write SQL UDF in Teradata

Hi 

Can  anybody help me write an SQL UDF to add days to a date ? 

It simply needs to be accepting a date and a number and adding them together to return another date

Please help .

Regards

Vishnu 

Teradata Employee

Re: write SQL UDF in Teradata

REPLACE FUNCTION ADD_DAYS(p1 DATE, p2 INTEGER)
RETURNS DATE
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN p1 + CAST(p2 AS INTERVAL DAY) ;
Teradata Employee

Re: write SQL UDF in Teradata

That worked like a charm , tomnolan ... Many thanks to you !!!!