Creating function returning DATE from table

Database
Fan

Creating function returning DATE from table

Hi Everyone,

We've got a need to create Teradata function (TD 15) which will return date from a table. Date in this table is changed by the other process. This is migration for existing process so it have to work like before.

More detail below:

CREATE MULTISET TABLE par_table (

par VARCHAR(10),

par_date DATE

)

UNIQUE PRIMARY INDEX (par);

insert into par_table values ('PAR1', current_date);

And now we need to call LAT_RUN_DATE function like this in SQL:

select k from t where t.CLOSED_DATE = LAST_RUN_DATE() - 1

As I know it is not posible to do a select statement in function body. Is is right? Is there any solution of this issue? We've got about 300 SQLs which usage of function so ot is not posible to change SQL code. Any suggestions?

2 REPLIES
Teradata Employee

Re: Creating function returning DATE from table

True, SQL UDFs cannot do SELECT.

Why not define a SQL UDF that simply returns a date which is specified as a literal in the function body, and change the other process to do REPLACE FUNCTION rather than UPDATE a row in a table?

Fan

Re: Creating function returning DATE from table

Updating table is obligatory because we migrate existing process. But your idea is very clever. Out tool is able to do select from table and then replace function.

So tanks very much for your help Fred.