All Forums Database
dzeron 2 posts Joined 09/13
27 Mar 2015
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?
 

Fred 1096 posts Joined 08/04
27 Mar 2015

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?
 

dzeron 2 posts Joined 09/13
31 Mar 2015

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.

You must sign in to leave a comment.