what wrong with my UDF use?

lenygold via DBMonster.com wrote:
Your function is defined on DATE. You call it with VARCHARs.
Try this:
CREATE FUNCTION dates(start VARCHAR(26), end VARCHAR(26))
RETURNS TABLE(dt date) RETURN SELECT * FROM TABLE(dates(date(start),
date(end))) AS D;

Lastly the function is a table function, so it needs to be called in the
FROM clause:
SELECT * FROM TABLE(dates('2007-01-01','2007-11-01')) AS D

You can a an integer counter with cut-off to the recursion so DB2
recognizes that the recursion in not infinite and avoids the warning
(not sure why it doesn't recognize the climbing date....)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

There are two isuues which I noticed.
1) Data type of parameters and arguments are differnt.
You can call it like this:
DATES(DATE('2007-01-01'), DATE('2007-03-01'))

or you can define it like this:
CREATE FUNCTION dates(start VARCHAR(10), end VARCHAR(10))
......

2) Syntax of calling TABLE UDF is as following.
SELECT *
FROM TABLE( DATES(DATE('2007-01-01'), DATE('2007-03-01')) ) AS d
;

Thank you very much for your help

Tonkuma wrote:

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums...bm-db2/200809/1