Tip

Recursive SQL

Recursive SQL doesn't exist on mainframe DB2 platforms and many MVS and z/OS installations have problems presenting recursive reports. Bill of Materials applications (BOM), for example, are a common requirement in many business environments. It is impossible to resolve the whole picture with controlled depth structure, in a single SQL statement. To illustrate the capability of a recursive common table expression for BOM applications, consider a table of parts with associated subparts and the quantity of subparts required by the part.

For this example, create the table as follows.

  CREATE TABLE PARTLIST
                 (PART VARCHAR(8),
                  SUBPART VARCHAR(8),
                  QUANTITY INTEGER);

In order to give query results for this example, assume the PARTLIST table is populated with the following values.

  PART     SUBPART  QUANTITY
  -------- -------- -----------
  00       01                 5
  00       05                 3
  01       02                 2
  01       03                 3
  01       04                 4
  01       06                 3
  02       05                 7
  02       06                 6
  03       07                 6
  04       08                10
  04       09                11
  05       10                10
  05       11                10
  06       12                10
  06       13                10
  07       14                 8
  07       12                 8

The example demonstrates the definition and use of a user-defined table function called RPLPART. This function is written in PL/I and returns a recursion report. The BOM is now resolved in an SQL statement with a table function.

CREATE FUNCTION SYSADM.RPLPART
(NPART              VARCHAR(8))
RETURNS TABLE
(RID                INTEGER           
 ,PART               CHAR(8)
 ,LEVEL              CHAR(9)
 ,SUBPART            CHAR(8)
 ,QUANTITY           INTEGER)
LANGUAGE PLI
READS SQL DATA
EXTERNAL NAME 'RPLPART'
SCRATCHPAD 100        
FINAL CALL
COLLID RPLPART           
PROGRAM TYPE SUB
WLM ENVIRONMENT DSNNWLM1;

The following select statement shows a recursive report and it answers the question: "What parts are needed to build the part identified by '01'?"

SELECT * FROM TABLE (SYSADM.RPLPART('01'))X

          RID  PART      LEVEL      SUBPART      QUANTITY
  -----------  --------  ---------  --------  -----------
            1  01        1          02                  2
            2  02         2         05                  7
            3  05          3        10                 10
            4  05          3        11                 10
            5  02         2         06                  6
            6  06          3        12                 10
            7  06          3        13                 10
            8  01        1          03                  3
            9  03         2         07                  6
           10  07          3        12                  8
           11  07          3        14                  8
           12  01        1          04                  4
           13  04         2         08                 10
           14  04         2         09                 11
           15  01        1          06                  3
           16  06         2         12                 10
           17  06         2         13                 10

Here is the source code:

* PROCESS SYSTEM(MVS);                                                 
 RSQL: PROC(PKEY,RID,PART,LEVEL,SUBPART,QUANTITY,                 
            PKEY_IV,RID_IV,PART_IND,LEVEL_IV,                       
            SUBPART_IV,QUANTITY_IV,                                   
            UDF_SQLSTATE,UDF_NAME,UDF_SPEC_NAME,                     
            UDF_DIAG_MSG,UDF_SCRATCHPAD,                              
            UDF_CALL_TYPE,UDF_DBINFO)                                 
          OPTIONS(FETCHABLE REENTRANT);                      
  DCL PKEY            CHAR(8) VAR;          
  DCL (RID,HPQ)       BIN FIXED(31);        
  DCL LEVEL           CHAR(9);              
  DCL (PART,SUBPART)  CHAR(8);           
  DCL QUANTITY        BIN FIXED(31);        
  DCL (HPP,HPD,HPART) CHAR(8);              
  DCL 1 UDF_SCRATCHPAD,                 
       3 UDF_SPAD_LEN    BIN FIXED(31),                                
       3 UDF_SPAD_TEXT   CHAR(100),                                    
        5 COUNTR         BIN FIXED(31);                               
  %INCLUDE UDFINFO;                         
  EXEC SQL INCLUDE SQLCA; 
  DCL RIDP(5000) BIN FIXED(31) STATIC;                                 
  DCL PARP(5000) CHAR(8) STATIC;                                       
  DCL LEVP(5000) CHAR(9) STATIC;                                       
  DCL SUBP(5000) CHAR(8) STATIC;                                       
  DCL QUAP(5000) BIN FIXED(31) STATIC;                                 
  DCL (K,IJ)     BIN FIXED(15) STATIC;                                
  IF COUNTR>5000 THEN UDF_SQLSTATE='02000';                            
  IF UDF_CALL_TYPE=-2 THEN DO;  
     DCL LEVELP  CHAR(9) INIT(' ');                 
     IJ=0;K=0;COUNTR=0;                                          
     CALL RECURS(PKEY,1);                               
     RECURS:PROC(PK,LEV) RECURSIVE;                     
            DCL (I,NUMD)         BIN FIXED(15);         
            DCL PK               CHAR(8) VAR;           
            DCL LEV              PIC '9';               
            HPART=PK;                                   
            EXEC SQL DECLARE C1 CURSOR WITH HOLD FOR    
                 SELECT PART,SUBPART,QUANTITY           
                 FROM PARTLIST                     
                 WHERE PART=:HPART                    
                 ORDER BY SUBPART;                      
            EXEC SQL SELECT COUNT(*)                    
                 INTO :NUMD                             
                 FROM PARTLIST                     
                 WHERE PART=:HPART;  
           IF NUMD=0 THEN RETURN;       
       BEGIN;                                                    
            DCL 1 TP(NUMD),                                      
                2 TSP CHAR(8),                                   
                2 TSD CHAR(8),                                   
                2 TSQ BIN FIXED(31);                             
            EXEC SQL OPEN C1;                                    
            DO I=1 TO NUMD;                                      
               EXEC SQL FETCH C1 INTO :HPP,:HPD,:HPQ;          
               TSP(I)=HPP;TSD(I)=HPD;TSQ(I)=HPQ;                                     
            END;                                                 
            EXEC SQL CLOSE C1;                                   
            DO I=1 TO NUMD;                                      
               K=K+1;                                            
               RIDP(K)=K;      
               SUBSTR(LEVELP,LEV,1)=LEV;                           
               LEVP(K)=LEVELP;                                     
               PARP(K)=TSP(I);                                     
               SUBP(K)=TSD(I);                                     
               QUAP(K)=TSQ(I);                                     
               LEVELP=' ';                                         
               CALL RECURS(SUBP(K),LEV+1);                         
            END;                                                   
     END;                                                          
     END RECURS;                                                   
  END;                                                             
  IF UDF_CALL_TYPE=0 THEN DO;                                      
     COUNTR=COUNTR+1;                                              
     IJ=IJ+1;                                                      
     IF IJ<=K THEN DO;                                             
        RID=RIDP(IJ);                                              
        LEVEL=LEVP(IJ);                                            
        PART=PARP(IJ);                                     
        SUBPART=SUBP(IJ);                                  
        QUANTITY=QUAP(IJ);                                 
     END;                                                  
     IF IJ>4999 ! IJ>K THEN UDF_SQLSTATE='02000';          
  END; 
  IF UDF_CALL_TYPE= 2 THEN UDF_SQLSTATE='02000';                                                            
 END RSQL; 

This was first published in May 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.