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

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