All Forums Database
parthmalhan 26 posts Joined 09/14
26 Oct 2014
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

Raja_KT 1246 posts Joined 07/09
27 Oct 2014

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.059.06.html, 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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

parthmalhan 26 posts Joined 09/14
27 Oct 2014

but i want to read data from database tables.
is this possible. if yes, will it hurt the performance?
 

Raja_KT 1246 posts Joined 07/09
27 Oct 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
27 Oct 2014

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

parthmalhan 26 posts Joined 09/14
28 Oct 2014

thanks all

03 Nov 2014

Hi Parth,
Can you please share how you resolved your SQL UDF problem.  My requirements are also similar.
Regards,
Amit.

vishaman 16 posts Joined 05/13
10 Dec 2014

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 

tomnolan 594 posts Joined 01/08
10 Dec 2014
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) ;

 

vishaman 16 posts Joined 05/13
10 Dec 2014

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

vishaman 16 posts Joined 05/13
11 Dec 2014

Hi Tomnolan 
I get this error while executing ....
select add_days(date,-210)
SELECT Failed. 7453:  Interval field overflow. 
Can you please let me know how to get rid of this ? 
Regards
Vishnu 
 
 
 
 

vishaman 16 posts Joined 05/13
11 Dec 2014

Hi Tomnolan 
 
I got it.... it was simple,had to increase the precision for day interval to 4 from default 2
 
Regards
Vishnu 

You must sign in to leave a comment.