Creating a SQL table function for RPGLE program

First of all, you have to create a User Defined Table Function.

Then you have to redesign you rpgle source, it will be constituted of:

• an open part,

• reading part

• and a close part

An example of my own:

SQL function table or also called User Defined Table Function

CREATE OR REPLACE FUNCTION (  char(5),
                                                            Numeric(9, 3,
                                                            boolean,
                                                           .
                                                           .
                                                            date)
  RETURNS TABLE( char(10), 
                 dec(11,3),
                 numeric(8,2),
                .
                .
                 boolean)
  EXTERNAL NAME 
  LANGUAGE RPGLE
  PARAMETER STYLE SQL
  MODIFIES SQL DATA
  NOT DETERMINISTIC
  DISALLOW PARALLEL
  SET OPTION DBGVIEW = SOURCE 

dcl-proc BSIGPWR;
  dcl-pi n extpgm;
    pInParam1 char(5) const;
    pInParam2 zoned(9:3) const;
    pInParam3 ind const;
                .
                .
    pInParamn date const;
    pOutparam1 char(10);
    pOutparam2 packed(11,3);
    pOutparam3 zoned(8,2);
                .
                .
    pOutParamn ind;
    nInParam1 int(5) const;
    nInParam2 int(5) const;
    nInParam3 int(5) const;
                .
                .
    nInParamn int(5) const;
    nOutparam1 int(5);
    nOutparam2 int(5);
    nOutparam3 int(5);
                .    
                .
    nOutParamn int(5);
    StateSQL         char(5);
    function          varchar(517) const;
    specific          varchar(128) const;
    errorMsg          varchar(70);
    CallType          int(10)      const;
  end-pi;
  // Constants to help with SQL parameters
  dcl-c CALLOPEN    -1;
  dcl-c CALLFETCH   0;
  dcl-c CALLCLOSE   1;   
// Select subroutines according to call type
  select;
  when CallType = CALLOPEN;
    exsr doOpen;
  when CallType = CALLFETCH;
    exsr doFetch;
  when CallType = CALLCLOSE;
    exsr doClose;
  endsl;
  return; 
  //----------------------------------------------------
  //  In this part, you will open files, cursors
  //----------------------------------------------------
  begsr doOpen;  
     open ;
     Exec sql Declare  cursor for 
     ;
     open ;
  endsr;
  //----------------------------------------------------
  //  In this part, you will read files, cursors and do you process
  //----------------------------------------------------
 begsr doFetch;
    // 
    if vcoduex = 0;
      StateSQL = '02000';
      leavesr;
    endif;
    // 
    // -------------------------------------
    // Récupération de l'intervention suivante
    // -------------------------------------
    exec sql fetch from  
                           into :,
                                :,
                                etc...;
    if sqlcode = 100;
      StateSQL = '02000';
      leavesr;
    endif; 
    Chain () ; 
  Endsr;
  //----------------------------------------------------
  //  Close cursor once on last call
  //----------------------------------------------------
  begsr doClose;
      // Closing the cursor
      exec sql close  ;
      // Closing file
      close *ALL; 
  endsr;
end-proc; 

Your can find all information in details page 34 of this documentation :
https://www.scottklement.com/presentations/RPG%20User%20Defined%20Functions%20%26%20Table%20Functions.pdf