SQl UDF - beyond SCALAR UDF

Extensibility
Enthusiast

SQl UDF - beyond SCALAR UDF

Hi,

Is it possible to create SQL UDF (not scalar) involving Select clause and database tables etc. In TD 13.10 we were not able to create the same but is it possible with higher TD releases (14.10 etc) ?
If not, then if we create a UDF in C/ JAVA is it possible to use TD database tables in those functions. Won't we have to provide the connection strings and SQL queries and login id/ password in the function to achieve the same ?

Are there any alternatives ?

Thanks,
Ajay
Tags (1)
3 REPLIES
Junior Contributor

Re: SQl UDF - beyond SCALAR UDF

Hi Ajay,

SQL UDFs don't support SELECTs in 15, too.

Of course you could write a C/Java UDF returning a result set, just don't ask me about the details, that's not my topic :-)

But are you sure you actually need it, can you explain what you actually try to do?

Enthusiast

Re: SQl UDF - beyond SCALAR UDF

Hi Dieter,

Thanks for your feedback and sorry for the delay. I was on vacation :-).
I need to create a function because the report users want to have flexibility to achieve desired results at run time. They were earlier using DWHS hosted on Oracle and had the luxury of passing parameters to functions and getting the desired result.
One of the e.g. is to convert a date time value to a particular timezone of a location. Since it could be any location that user can pass so they need flexibility.

Well we were able to create simple Java UDF and host them on TD 13.10 as well but now are facing challenges in trying to execute some sql statements. For that we are trying to establish connection to TD environment from within the TD UDF function in java and I can't get the connection established. I am getting exceptions in following string:

Connection con = DriverManager.getConnection( "jdbc:default:connection" );

I know you said its not your topic but any inputs or references would be helpful.

Thanks,
Ajay
Teradata Employee

Re: SQl UDF - beyond SCALAR UDF

Ajay,

I recommend my blog entry on this topic: http://developer.teradata.com/blog/georgecoleman/2014/01/ordered-analytical-functions-translating-sq... It seems to me impractical to allow functions to issue SQL in a parallel processing architecture.  This blog entry explains how to implement that function-ality (so to speak) in a derived table.

Otherwise, in general, one can create a Table Operator (see the SQL External Routine Programming manual) to read from a table and produce another table as output, and you could adapt this Java code as a table operator.  I am not sure how the users are using this converted date, but I am sure that this functionality could be done, as either a derived table or a table operator.

George