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 (
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?
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?
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.