Re: SQL UDF not found?

[fkany@xxxxxxxxxxxxxxxxxx]

Try checking the file QSYS2/SYSROUTINE to see if the function is named
correctly.

hth,

Frank





Ben_Pforsich@xxxxxxxxxxxx@midrange.com on 10/14/2005 09:07:19 AM

Please respond to RPG programming on the AS400 / iSeries
       <rpg400-l@xxxxxxxxxxxx>

Sent by:    rpg400-l-bounces+fkany=averittexpress.com@xxxxxxxxxxxx


To:    rpg400-l@xxxxxxxxxxxx
cc:

Subject:    SQL UDF not found?


I am trying to write a very simple SQL UDF from an RPGLE module in a
SRVPGM.  I've registered the function ALPHAITEM which is same name as the
subprocedure exported in the module, but I am consistently getting the
error message "ALPHAITEM in *LIBL type *N not found." when I try to execute
the following SQL from STRSQL and from an SQLRPGLE program:

select ALPHAITEM(s20prd)
from rsp200 where s20cmp = 'B1'

Below is the code with the steps I followed to create the service program
and register the SQL function.  Any help would be greatly appreciated.
BTW, I have read the following article which was helpful in getting me
started:
http://faq.midrange.com/data/cache/185.html

<code>
     h nomain
     h debug
     h option(*nodebugio : *nosrcstmt)

*************************************************************************
      * Compile Instructions

*------------------------------------------------------------------------
      * 1. Create RPGLE module from this source member:
      *    CRTRPGMOD MODULE(ISSBDP/ALPHAITEM) SRCFILE(ISSBDP/QRPGLESRC)
      *              SRCMBR(ALPHAITEM)
      *
      * 2. Create/update SQLUDF service program in ISSBDP with this module:
      *    CRTSRVPGM SRVPGM(ISSBDP/SQLUDF) MODULE(ISSBDP/ALPHAITEM)
      *              EXPORT(*ALL)
      *
      * 3. Register this function by executing the following in STRSQL:
      *    CREATE FUNCTION ISSBDP/ALPHAITEM(INT) RETURNS CHAR(15)
      *    LANGUAGE RPGLE
      *    DETERMINISTIC
      *    NO SQL
      *    RETURNS NULL ON NULL INPUT
      *    NO EXTERNAL ACTION ALLOW PARALLEL
      *    SIMPLE CALL
      *    EXTERNAL NAME 'ISSBDP/SQLUDF(ALPHAITEM)'

*************************************************************************
      * Procedure prototypes

*------------------------------------------------------------------------
      * AlphaItem function
      *   returns the alphanumeric representation of a numeric item so that
it
      *   contains at least 4-digits (ex: 23 -> '0023', 5603 -> '5603')
     d AlphaItem       pr            15a
     d   numItem                     10p 0


*************************************************************************
      * Procedure interfaces

*------------------------------------------------------------------------
     p AlphaItem       b                   export

*------------------------------------------------------------------------
     d AlphaItem       pi            15a
     d   numItem                     10p 0

*------------------------------------------------------------------------
     d Result          s             15a
      /free

       select;
         when numItem < 10;
           Result = '000' + %char(numItem);
         when numItem < 100;
           Result = '00' + %char(numItem);
         when numItem < 1000;
           Result = '0' + %char(numItem);
         other;
           Result = %char(numItem);
       endsl;

       return Result;

      /end-free
     p AlphaItem       e
</code>

Ben Pforsich
Programmer Analyst
Bob Evans Farms, Inc.
I/S Department
Columbus, Ohio
Ben_Pforsich@xxxxxxxxxxxx

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.