need help with UDF

[lenygold via DBMonster.com]

MAXRC is UDF Returns GREATEST Value of list of COLUMNS – TYPE CHARACTER
Example of use:
SELECT EMPNO, FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,
MAXRC(FIRSTNME,MIDINIT,LASTNAME,WORKDEPT) AS MAX_IN_ROWS
FROM EMPLOYEE;

FIRSTNME MIDINIT LASTNAME WORKDEPT
MAX_IN_ROWS

------------ ------- --------------- -------- --------------------------------
----------------------------------------------------
CHRISTINE I HAAS A00
I

MICHAEL L THOMPSON B01
THOMPSON

SALLY A KWAN C01
SALLY

JOHN B GEYER E01
JOHN

IRVING F STERN D11
STERN

EVA D PULASKI D21
PULASKI

But when i try use it with Temp tables it is not working:

WITH T1(D_KEY,D_NAME) AS
(VALUES(1,'Barbara'),
(2,'John'),
(3,'Steve')),
T2 (T_KEY,T_NAME) AS
(VALUES (1,'Wattson'),
(2, 'Jackson'),
(3, 'Henry'),
(1,'Smith'))

SELECT D_NAME,T_NAME,MAXRC(T1.D_NAME,T2.T_NAME)
FROM T1,T2;
i got following error message
No authorized routine named "MAXRC" of type "FUNCTION
" having compatible arguments was found.

sqlcode: -440

Any ideas what is wrong?
Thank's in advance Leny G.

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

[lenygold via DBMonster.com]

lenygold wrote:

>MAXRC is UDF Returns GREATEST Value of list of COLUMNS – TYPE CHARACTER
Example of use:
SELECT EMPNO, FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,
MAXRC(FIRSTNME,MIDINIT,LASTNAME,WORKDEPT) AS MAX_IN_ROWS
FROM EMPLOYEE;

FIRSTNME MIDINIT LASTNAME WORKDEPT
MAX_IN_ROWS

------------ ------- --------------- -------- --------------------------------
----------------------------------------------------
CHRISTINE I HAAS A00
I

Never mind. I find what is wrong. My Udf started with 3 arguments and more. I
fixed it

>
MICHAEL L THOMPSON B01
THOMPSON

SALLY A KWAN C01
SALLY

JOHN B GEYER E01
JOHN

IRVING F STERN D11
STERN

EVA D PULASKI D21
PULASKI

But when i try use it with Temp tables it is not working:

WITH T1(D_KEY,D_NAME) AS
(VALUES(1,'Barbara'),
(2,'John'),
(3,'Steve')),
T2 (T_KEY,T_NAME) AS
(VALUES (1,'Wattson'),
(2, 'Jackson'),
(3, 'Henry'),
(1,'Smith'))

SELECT D_NAME,T_NAME,MAXRC(T1.D_NAME,T2.T_NAME)
FROM T1,T2;
i got following error message
No authorized routine named "MAXRC" of type "FUNCTION
" having compatible arguments was found.

sqlcode: -440

Any ideas what is wrong?
Thank's in advance Leny G.

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

[Serge Rielau]

Either redefine your function to accept VARCHAR, or cast your input to CHAR.
CHAR is lower in the promotion chain that VARCHAR.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab