What to Do

SQL Programming Techniques › Bill-of-materials Explosion › What to Do

What to Do

Maximum Level

The sample program establishes a value of 100 as the limit of levels for the explosion in its use of the MAX-LEVELS variable. A limit of 100 is for illustration only; a program can set a higher or lower limit.

LIMITS-AND-CONSTANTS.
    02  NUMBER-OF-CURSORS  PIC S9    COMP VALUE 3.
    02  MAX-LEVELS         PIC S9(4) COMP VALUE 100.
    02  NULL-KEY-VALUE     PIC 9(7)       VALUE 0.

Cursor Declarations

The program declares three different cursors with identical definitions. The cursor issues a join of the PART and COMPONENT tables that produces a result table of component parts for each part.

 EXEC SQL  DECLARE CURSOR1 CURSOR FOR
     SELECT COMPONENT_PART,
            QUANTITY,
            PART_NAME
       FROM COMPONENT C,
            PART P
      WHERE C.PART = :CURRENT-KEY
        AND C.COMPONENT_PART > :PREVIOUS-COMPONENT
        AND P.NUMBER = C.PART
      ORDER BY COMPONENT_PART
 END-EXEC

 EXEC SQL  DECLARE CURSOR2 CURSOR FOR
     SELECT COMPONENT_PART,
            QUANTITY,
            PART_NAME
       FROM COMPONENT C,
            PART P
      WHERE C.PART = :CURRENT-KEY
  .
  .
  .

The minimum number of cursors needed is two. Theoretically, the program could declare more cursors with identical definitions, up to a number of cursors equal to the maximum level for the explosion. However, for most bill-of-material explosions, it is more practical and efficient to add program logic that allows the three cursors to be reused as illustrated in the sample program later in this section.

Getting the First Row

The GET-FIRST-ROW section of the program issues a single-row select from the PART table. The search condition equates an input part number (TOP-KEY), the part to be exploded, with PART_NUMBER, the unique key of the PART table.

This select verifies the existence of the part and also retrieves its name.

EXEC SQL
  SELECT  PART_NUMBER,  PART_NAME
    INTO :CURRENT-KEY, :COMPONENT-NAME
    FROM  PART
    WHERE PART_NUMBER = :TOP-KEY
END-EXEC.

Going to the First Level

In the FETCH-NEXT-ROW section, the program opens a cursor to retrieve the component parts that make up the current part, whose number it has assigned to CURRENT-KEY. The program fetches the first row of the cursor result table.

FETCH-NEXT-ROW SECTION.
    PERFORM OPEN-CURRENT-CURSOR.

    IF      CURRENT-CURSOR = 1
       EXEC SQL
          FETCH CURSOR1 INTO
            :COMPONENT-KEY, :QTY, :COMPONENT-NAME
       END-EXEC
    ELSE IF CURRENT-CURSOR = 2
 .
 .
 .

Going Down More Levels

If the first fetch succeeds, the program executes the DOWN-ONE-LEVEL section. In this section, the program:

Assigns the part number in the first row fetched to CURRENT-KEY
Increments the current level by 1
Increments the current cursor by 1 if the current cursor is less than 3

Because the program reuses the three cursors, it attempts to close a cursor in the CLOSE-CURRENT-CURSOR section before it opens the cursor in the OPEN-CURRENT-CURSOR section. For the first three levels of the explosion, the DBMS will ignore the CLOSE statement because the specified cursor has not yet been opened.

Using the part number retrieved in the fetch by the previous cursor, the program now fetches the first component part of the next level down by opening the current cursor and fetching from it. This logic is repeated until a fetch returns an SQLCODE of 100 (in effect, no more levels) or the defined maximum level is reached.

Saved Keys

Each time it goes down a level, the program saves the part number used in the fetch:

DOWN-ONE-LEVEL SECTION.
    IF CURRENT-LEVEL > MAX-LEVELS
       NEXT SENTENCE
    ELSE
       MOVE COMPONENT-KEY TO CURRENT-KEY
       MOVE COMPONENT-KEY TO SAVE-KEY (CURRENT-LEVEL)
 .
 .
 .

By saving the key, the program can later retrieve the part number for a level and execute the backup logic described below.

When There Are No More Levels

When there are no more levels, the program executes the BACKUP-ONE-LEVEL section. It subtracts 1 from the level number and retrieves the saved keys for the current and previous levels.

BACKUP-ONE-LEVEL SECTION.
    SUBTRACT 1 FROM CURRENT-LEVEL.
    IF CURRENT-LEVEL > 0
       MOVE SAVE-KEY (CURRENT-LEVEL) TO PREVIOUS-COMPONENT.
    IF CURRENT-LEVEL > 1
       MOVE SAVE-KEY (CURRENT-LEVEL - 1) TO CURRENT-KEY
.
.
.

Since the cursor result tables are ordered by component part number and one of the conditions of each is C.COMPONENT_PART > :PREVIOUS-COMPONENT, the program re-establishes cursor position in the list of components by limiting the rows selected to those not yet processed. Each time a cursor is re-opened, the first row of the result table is the next component to be processed,

This allows the program both to reuse a cursor and to fetch the next row for the previous level.

Completing the Explosion

The process of going down a level until there are no more levels, going back one level, and attempting to go down again is repeated until backing up reaches the top level. The bill-of-materials explosion is now complete.