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



Frank, I wondered where where SQL was registering these functions because I
never found an "object" out there for it.  Thanks.
Here's what's in QSYS2/SYSROUTINE.  I don't see anything out of the
ordinary:

           SPECIFIC_SCHEMA  ISSBDP
             SPECIFIC_NAME  ALPHAITEM
            ROUTINE_SCHEMA  ISSBDP
              ROUTINE_NAME  ALPHAITEM
              ROUTINE_TYPE  FUNCTION
           ROUTINE_CREATED  2005-10-14-09.56.36.478000
           ROUTINE_DEFINER  ISSBDP
              ROUTINE_BODY  EXTERNAL
             EXTERNAL_NAME  ISSBDP/SQLUDF(ALPHAITEM)
         EXTERNAL_LANGUAGE  RPGLE
           PARAMETER_STYLE  GENERAL
          IS_DETERMINISTIC  YES
           SQL_DATA_ACCESS  NONE
                  SQL_PATH
            PARM_SIGNATURE  0
         NUMBER_OF_RESULTS     1
  MAX_DYNAMIC_RESULT_S ETS
                  IN_PARMS     1
                 OUT_PARMS
               INOUT_PARMS
                PARSE_TREE
           FUNCTION_ORIGIN  E
             FUNCTION_TYPE  S
           EXTERNAL_ACTION  N
              IS_NULL_CALL  NO
               SCRATCH_PAD
                FINAL_CALL  NO
            PARALLELIZABLE  YES
                    DBINFO  NO
      IS_USER_DEFINED_CAST  NO
               CARDINALITY
                    FENCED  YES
          COMMIT_ON_RETURN  NO
               IASP_NUMBER
       NEW_SAVEPOINT_LEVEL  NO
              LAST_ALTERED  0001-01-01-00.00.00.000000
                DEBUG_MODE  0



                                                                           
             fkany@averittexpr                                             
             ess.com                                                       
             Sent by:                                                   To 
             rpg400-l-bounces@         CN=RPG programming on the           
             midrange.com              AS400/O=iSeries                     
                                       <rpg400-l@xxxxxxxxxxxx>             
                                                                        cc 
             10/14/2005 10:20                                              
             AM                                                    Subject 
                                       Re: SQL UDF not found?              
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           






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.




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





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.