need help with UDF

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • lenygold via DBMonster.com
    #1

    need help with UDF

    MAXRC is UDF Returns GREATEST Value of list of COLUMNS – TYPE CHARACTER
    Example of use:
    SELECT EMPNO, FIRSTNME,MIDINI T,LASTNAME,WORK DEPT,
    MAXRC(FIRSTNME, MIDINIT,LASTNAM E,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,'Barb ara'),
    (2,'John'),
    (3,'Steve')),
    T2 (T_KEY,T_NAME) AS
    (VALUES (1,'Wattson'),
    (2, 'Jackson'),
    (3, 'Henry'),
    (1,'Smith'))

    SELECT D_NAME,T_NAME,M AXRC(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.aspx/ibm-db2/200803/1


    Tags: None
    • lenygold via DBMonster.com
      #2
      Re: need help with UDF

      lenygold wrote:
      >MAXRC is UDF Returns GREATEST Value of list of COLUMNS – TYPE CHARACTER
      >Example of use:
      >SELECT EMPNO, FIRSTNME,MIDINI T,LASTNAME,WORK DEPT,
      MAXRC(FIRSTNME, MIDINIT,LASTNAM E,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,'Barb ara'),
      (2,'John'),
      (3,'Steve')),
      T2 (T_KEY,T_NAME) AS
      (VALUES (1,'Wattson'),
      (2, 'Jackson'),
      (3, 'Henry'),
      (1,'Smith'))
      >
      SELECT D_NAME,T_NAME,M AXRC(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.aspx/ibm-db2/200803/1


        Comment

        • Serge Rielau
          #3
          Re: need help with UDF

          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

            Comment

            Previous template Next
            Working...