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