Archives of the TeradataForum
Message Posted: Wed, 11 Feb 2015 @ 21:44:56 GMT
I would really like to create an SQL Scalar Function which returns the single value of a Select Statement, so something like this :
CREATE FUNCTION DeriveYearWeekNr ( nF_NumberOfWeeks INTEGER ) RETURNS INTEGER LANGUAGE SQL CONTAINS SQL DETERMINISTIC SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN ( SELECT MIN(((CAL_PAST.TNT_YR*100)+CAL_PAST.TNT_WK)) AS PAST_YRWK FROM MYDB.CALDAYSDIFF_V01 CAL_PAST WHERE CAL_PAST.WKS_DIFF = nF_NumberOfWeeks ) ;
(Actually the SQL is a little bit more complicated than the above, but it serves to illustrate the point .... i hope)
What I get is 6881 (SELECT is no allowed in RETURN statement of and SQL UDF).
Thing is, the definition does say 'CONTAINS SQL' and that the language is SQL.
So, have I missed the point here, I wonder ... can you only use SQL type functions, like MAX, CASE, etc, or have I just got the syntax of the RETURN statement wrong ?
Anyone know ?
Oh, by the way, we're on v14.10
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|