01 - Programming (EN)01a - RPG (EN)01e - Programming miscellanea (EN)

Recursive calls in ILE RPG and SQL – part 1

Last Updated on 13 June 2021 by Roberto De Pedrini

Can you make a recursive call in RPG? No, impossible !
This is often the first answer you get to this question … That’s true, but only partially. Actually, in RPG it is not possible to recursively call a program. But an ILE program can have procedures, and here is another matter …
In fact, thanks to their specificity and their isolation level, procedures can be called recursively: after all, a procedure is a function that receives parameters, and returns a return value (or none). This feature means that a call to a procedure can safely take place even within the procedure itself. Everything is about properly writing the code, taking into account recursion.

Here we see a typical situation in which recursion allows to have a much simpler code: a bill of materials’ development.
For this example we created a master file with the item type and code, the component type and code, and the required quantity. The item type identifies the type of product (end, semi-assembled, etc …). Each item can obviously have more components, so in the file there will be one or more records for each “parent” item, and the same happens for its components (which in turn are “parents” of other items).
Here is the SQL statement for creating the table.

CREATE OR REPLACE TABLE BOMDT00F (                 
 ITEMTYPE CHAR(1)       NOT NULL DEFAULT ' ' ,     
 ITEMCODE CHAR(20)      NOT NULL DEFAULT ' ' ,     
 COMPTYPE CHAR(1)       NOT NULL DEFAULT ' ' ,     
 COMPCODE CHAR(20)      NOT NULL DEFAULT ' ' ,     
 QUANTITY NUMERIC(9, 2) NOT NULL DEFAULT 0 )       
                                                   
 RCDFMT BOMDT   ;    

ITEMTYPE and ITEMCODE columns identify the “parent” article, while COMPTYPE and COMPCODE are the component.
An index has been created over this table:

CREATE INDEX BomDT01L ON BomDT00F (  
 itemType, itemCode                  
 )                                   
 RCDFMT BOMDT   ;  

These are the sample data for our test.

The sample program reads the BOM and writes the results to a work file. Starting from an initial item and type, received as input parameter, the program searches for its components, and for each component it calculates the requirement, writing the output to the work file. The process is then recursively repeated for each sub-component, down one level until reaching the raw materials (items that have no components). When using recursive procedures it is important to always set an exit condition, otherwise you could get caught into an infinite loop.
Below is the definition of the work file:

CREATE OR REPLACE TABLE BOMWRK (                 
 LEVEL    NUMERIC(4, 0),                         
 ITEMTYPE CHAR(1)       NOT NULL DEFAULT ' ' ,   
 ITEMCODE CHAR(20)      NOT NULL DEFAULT ' ' ,   
 COMPTYPE CHAR(1)       NOT NULL DEFAULT ' ' ,   
 COMPCODE CHAR(20)      NOT NULL DEFAULT ' ' ,   
 QUANTITY NUMERIC(9, 2) NOT NULL DEFAULT 0 )     
                                                 
 RCDFMT WBOM   ;   

Here the full source of sample program (FAQ40014B):

       ctl-opt decedit('0,') datedit(*dmy.)
       actgrp(*caller) dftactgrp(*no) option(*nodebugio:*srcstmt);
      //-------------------------------------------------------------*
      // Bill Of Material development
      //
      // Reads the BOM from base item and develops all components
      //  and related required quantities, down to raw material.
      // Output is written to a work file
      //
      // INPUT PARMS:
      //  - item type     CHAR(1)
      //  - item code     CHAR(20)
      //-------------------------------------------------------------*
      // BOM master file
       dcl-f BomDT01l  disk usage(*input) keyed;
       // work file
       dcl-f BomWRK    disk usage(*output) prefix(W_);
      // ENTRY     -------------------------------------------------
       dcl-pi FAQ40014B;
           dcl-parm pType    char(1);
           dcl-parm pCode    char(20);
       end-pi;
      // Prototypes ------------------------------------------------
       dcl-pr getBOM;
           *n char(1);
           *n char(20);
           *n zoned(9:2);
           *n zoned(4:0);
       end-pr;
      // Variables   -----------------------------------------------
       dcl-ds dsBOM     extname('BOMDT00F') end-ds;
       dcl-s  qty0      like(Quantity);
       dcl-s  level0    zoned(4:0);
      // PSDS ------------------------------------------------------
       dcl-ds pgmDS psds qualified;
           user     char(10) pos(254);
       end-ds;
      //*********************************************************
      // SQL options
       Exec SQL
          SET OPTION COMMIT = *None,
          ALWCPYDTA = *Yes,
          CLOSQLCSR = *EndMod,
          DLYPRP    = *Yes
         ;
      //  Clear work file
        Exec SQL
        DELETE FROM BomWRK ;

      // Get initial item
        chain (pType:pCode) BomDT01l;
        if %found(BomDT01l);
           qty0 = 1;
           level0 = 1;
           getBOM(pType:pCode:qty0:level0);
        endif;

        close *all;
        *inLR = *on;
        return;
        // --------------------------------------------------
        // BOM development
        // --------------------------------------------------
       dcl-proc getBOM;
      // Procedure interface -------------------
         dcl-pi getBOM ;
             dcl-parm   pType  char(1);        // item type
             dcl-parm   pItem  char(20);       // item code
             dcl-parm   pQty   zoned(9:2);     // required quantity
             dcl-parm   pLevel zoned(4:0);     // depth level
         end-pi;
      // BOM master file
       dcl-f BomDT01l  disk usage(*input) keyed;
      // Variables -------------------------------------------------
       dcl-s qtyComp    like(Quantity);
       dcl-s level      zoned(4:0);
       dcl-ds dsBOM2    extname('BOMDT01L':*all) end-ds;

        // read item's components and develop BOM
        setll (pType:pItem) BomDT01l;
        level = pLevel + 1;
        dou %eof(BomDT01l);
            reade (pType:pItem) BomDT01l dsBOM2;
            if not %eof(BomDT01l) and CompCode <> *blank;
               eval(h) qtyComp = pQty * quantity;
               exsr wrtFile;
               // recursive call
               getBOM(CompType:
                      CompCode:
                      qtyComp:
                      level);
            endif;
        enddo;

        // --------------------------------------------------
        // write item component to work file
        // --------------------------------------------------
        begsr wrtFile;
                Clear WBOM;
                W_Level    = pLevel;
                W_itemType = pType;
                W_itemCode = pItem;
                W_compType = compType;
                W_compCode = compCode;
                W_quantity = qtyComp;
                Write WBOM;
        endsr;
        // --------------------------------------------------
       end-proc;

Let’s examine in detail the getBOM procedure: the input parameters are type and code of the “parent” item and the related required quantity. The procedure reads all the parent item’s components, calculates the requirement, writes a record to the work file and recursively calls the same getBOM procedure passing as parameters the type and code of the component, along with the calculated required quantity, which then propagates to lower level. Notice how the BOMDT01L file was declared locally in the procedure; in this way, with each recursive call (which actually creates a new instance of the procedure in the call stack), record positioning on the file is not lost within read loops by key.
After program run, the work file will include the full development of the bill of materials, down to raw materials with the related requirements. Launching the program for type 4 MADRID article, here is the result:

The LEVEL column shows the development depth: 1 is the end product, 2 the first semi-assembled product, etc. .. in this case it stops at level 3, whose components are the raw materials. From the content of this file it is therefore clear that in order to produce a unit of end product (article 4-MADRID), three raw materials are needed, WOOL-002, POLY-001, SILK-002 with a requirement of respectively 144, 104 and 14.29 “units” (to stay generic, no specific units of measurement are indicated).
The use of recursive procedures considerably eases some typical application situations, in addition to the bill of materials just think about the development of a menu tree, or the definition of routes in the logistics industry, with departures and arrivals.
In the next part we will see how SQL is not far behind and also allows you to manage recursion.

All sources of this example are available on GitHub at this link: https://github.com/MD2706GIT/FAQ400

DOCUMENTATION
IBM: Recursive Calls – https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzasc/recursv.htm

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

Senior IBM i Analyst/Developer and technical writer. Former collaborator of the Italian edition of "System i News" magazine and author of several publications about tools and development practices for the IBM i platform.

Leave a Reply

Your email address will not be published. Required fields are marked *