× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.