what wrong with my UDF use?

[lenygold via DBMonster.com]

CREATE FUNCTION dates(start DATE, end DATE)
RETURNS TABLE(dt DATE)
RETURN
WITH REC (DT)
AS
(VALUES(DATE(START))
UNION ALL
SELECT DT + 1 DAY FROM REC WHERE DT < END)
SELECT DT FROM REC;

SELECT DATES('2007-01-01','2007-11-01')
FROM SYSIBM.SYSDUMMY1;
and i got an error:

sqlcode: -440

sqlstate: 42884
No authorized routine named "DATES" of type "FUNCTION
" having compatible arguments was found.

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

[Serge Rielau]

lenygold via DBMonster.com wrote:

CREATE FUNCTION dates(start DATE, end DATE)
RETURNS TABLE(dt DATE)
RETURN
WITH REC (DT)
AS
(VALUES(DATE(START))
UNION ALL
SELECT DT + 1 DAY FROM REC WHERE DT < END)
SELECT DT FROM REC;

SELECT DATES('2007-01-01','2007-11-01')
FROM SYSIBM.SYSDUMMY1;
and i got an error:

sqlcode: -440

sqlstate: 42884
No authorized routine named "DATES" of type "FUNCTION
" having compatible arguments was found.

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

[Tonkuma]

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
;

[lenygold via DBMonster.com]

Thank you very much for your help

Tonkuma wrote:

>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
;

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