Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Question Unanswered: Cannot call UDF on AS/400 V5R1

    AS/400 V5R1, DB/2

    I am creating a new UDF (first one) and the creation is working fine (assumed) ... I get my service program created in my jasher library.

    However, when I try to use the UDF, I get an error message..

    Since I am NEW to the UDFs on the AS400, I am wondering if I am missing a piece. My library list includes my library, I have access to the libraries/files. I can run all this interactively on my workstation (via a Query tool) or in STRSQL and it works fine.

    I cannot seem to access the UDF that was created...

    ANY help will be greatly appreciated !!!

    Thanks in advance.

    (error message)
    <eb1>SQL0204 - TRANSSUM in JASHER type *N not found.
    State0002,Native:-204,Origin:[IBM][iSeries Access ODBC Driver][DB2 UDB]</eb1>

    (udf definition)
    Create Function jasher.transsum(intsrefn char(7), intsco integer, intscode char(2), intspn char(15), intsloct char(9)) returns integer
    Language SQL
    Reads SQL data
    value(sum(tstqty),0) from lib1.table1 where tsco = intsco and tscode = intscode and tsloct = intsloct and tsrefn in (intsrefn,
    when left(intsrefn,2) = '00' then ' '||substr(intsrefn,3)
    when left(intsrefn,1) = '0' then ' '||substr(intsrefn,2)
    else intsrefn end
    and tspn = intspn

  2. #2
    Join Date
    Oct 2003

    Found problem...

    The problem seems to be with the conversion of data from the native DB2 format to the Function format.

    I used char(15) for a A(15) formated item. Char(15) for the input definition... the item is an A(15) in the database... I was trying to pass the variables directly in the function call.

    This did not work... I had to cast the varables to the exact definition... thus I had to do the following:

    function_name(cast(table.variabe as char(15)), cast(....))

    in the sql calling statement... arg!!

    It seems the error NOT FOUND also means ... "Varables do not match a Function defined..."


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts