This document actually contains 3 source files:

- IMP_MRP4R – The Raw data input

- IMP_MRP4P – The Processed ouput

- PARSE_MRP4 – The Stored Procedure which actually does the parsing job.

This is a simple parsing routine, which shows how a procedure allows one to use

two cursors, operating one within the other.

- One cursor is used to read each row

- One cursor is used to break apart each row content, splitting it into separate

output rows.

--------------------------------------------------------------------------------

-- Input table, containing text data. Each description is split with a "~"

-- character. This was used so that the table could be easily FTP-ed to

-- the iSeries without causing new records each time a carriage return was

-- found. The idea is to split each of these "sentences" and to create a new

-- record with the original material number found at the beginning of each

-- row.

--------------------------------------------------------------------------------

--/////////////////////////////////////////////////////////////////////////////

--------------------------------------------------------------------------------

-- Input table to contain un-parsed data

-- In this case, the data was originally in an XL table, it was then modified

-- to change the manual line feeds an replace them with “~” characters, so that

-- they would not affect the FTP process (see text further down).

--------------------------------------------------------------------------------

CREATE TABLE IMP_MRP4P

(

MATERIAL_NUMBER FOR HD_DT INT WITH DEFAULT 0 ,

TEXT_1 FOR PARSED1 CHAR(512)

) ;

--/////////////////////////////////////////////////////////////////////////////

-- Output table to contain descriptions, parsed from comma-delimited

-- IMP_MRP4R, now in table format.

--------------------------------------------------------------------------------

CREATE TABLE IMP_MRP4R

(

MATERIAL_NUMBER FOR HD_DT INT WITH DEFAULT 0 ,

TEXT_1 FOR ITMN1 CHAR(512) WITH DEFAULT ' ',

TEXT_2 FOR ITMN2 CHAR(512) WITH DEFAULT ' '

) ;

--/////////////////////////////////////////////////////////////////////////////

Here below is the stored procedure

--\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

-------------------------------------------------------------------------------

-- Name: PARSE_MRP4

-- Description: Stored procedure to read IMP_MRP4R and parse the

-- contents into a new table, IMP_MRP4P.

-- Author : Thibault Dambrine

-- Date : October 3, 2008

-------------------------------------------------------------------------------

-- IN: IMP_MRP4R (Raw MRP4 data)

-- OUT: IMP_MRP4P (Parsed MRP4 data)

-------------------------------------------------------------------------------

-- Notes: Prior steps in XL to re-format the tables sent by provider:

-- This is necessary to FTP the original data without unwanted

-- carriage returns, which cause FTP to create new records in

-- places where we really don't need them.

-- 1) Make a .csv copy of the original, leave only item number,

-- original description,

-- cleansed description

-- 2) Copy these three columns into a WORD document

-- 3) Global replace the manual line breaks (^l) with a squiggle (~) character

-- 4) Grab the Word document contents and re-insert into Excel

-- 5) Import into CAAS01

-- 6) Where Cleansed document = ' ', update with original

-- 'update EPBWIPMC2/IMP_MRP4R set text_2 = text_1 where text_2 = '' '' '

-- 7) Remove NULL values

-- 'delete from EPBWIPMC2/IMP_MRP4R where material_number is null '

-- In concrete terms, an XL set of cells looking like

-- ________________________________________________________________________________

-- |material_number | Description |

-- |_________________|_____________________________________________________________|

-- |1234567890 |Descriptor: middle bolt, rising stem ^l |

-- | |Diameter: 48 Inches ^l |

-- | |Connection ends: flange ^l |

-- | |Spec: (ASME/ANSI, B16.5A, ASME, B16.47), SW (ASME B16.25) ^l |

-- | |Operator: handwheel, gear, subulate gear, actuator ^l |

-- | |Sealing material: F6, F310, CoCrA, Cu, MoNCL ^l |

-- |_________________|_____________________________________________________________|

-- (note: the "^l" characters are not visible in XL - they

-- just trigger the carriage returns)

-- will enter as follows in an output table.

-- material_number Description

-- =============== ==========================================================

-- 1234567890 Descriptor: middle bolt, rising stem

-- 1234567890 Diameter: 48 Inches

-- 1234567890 Connection ends: flange

-- 1234567890 Spec: (ASME/ANSI, B16.5A, ASME, B16.47), SW (ASME B16.25)

-- 1234567890 Operator: handwheel, gear, subulate gear, actuator

-- 1234567890 Sealing material: F6, F310, CoCrA, Cu, MoNCL

-------------------------------------------------------------------------------

CREATE PROCEDURE PARSE_MRP4

LANGUAGE SQL

SET OPTION OUTPUT = *PRINT, DBGVIEW = *SOURCE

BEGIN

--------------------------------------------------------------------------------

-- Declare variables section --

--------------------------------------------------------------------------------

-- Declare string parsing Intake Variables

DECLARE W_MATERIAL_NUMBER INT ;

DECLARE W_TEXT_1 CHAR(512) ;

DECLARE W_TEXT_2 CHAR(512) ;

DECLARE W_NOTES_RRN INT ;

-- Declare string parsing Variables

DECLARE STRING_START INT DEFAULT 0 ;

DECLARE STRING_END INT DEFAULT 0 ;

DECLARE STRING_LENGTH INT DEFAULT 0 ;

DECLARE NEXT_START INT DEFAULT 0 ;

DECLARE END_OF_FILE INT DEFAULT 0 ;

DECLARE INNER_LOOP INT DEFAULT 0 ;

-- SQL System Monitoring Variables and work variables --

DECLARE SQLCAID CHAR(8) DEFAULT 'NO VALUE' ;

DECLARE SQLCABC INT DEFAULT 0 ;

DECLARE SQLCODE INT DEFAULT 0 ;

DECLARE SQLERRML SMALLINT DEFAULT 0 ;

DECLARE SQLERRMC CHAR(70) DEFAULT 'NO VALUE' ;

DECLARE SQLERRP CHAR(8) DEFAULT 'NO VALUE' ;

DECLARE SQLERRD CHAR(6) DEFAULT 'NO VAL' ;

DECLARE SQLWARN CHAR(11) DEFAULT 'NO VALUE' ;

DECLARE SQLSTATE CHAR(5) DEFAULT '00000' ;

-- SQL User Monitoring Variables and work variables --

--------------------------------------------------------------------------------

-- Declare Cursor to get the staging data one record at a time. --

--------------------------------------------------------------------------------

DECLARE CURSOR_01 CURSOR FOR

SELECT

notes.MATERIAL_NUMBER , -- Item Number

notes.TEXT_1 , -- Original Text

notes.TEXT_2 , -- Clean Text

rrn(notes) -- Original RRN

FROM

IMP_MRP4R NOTES ;

--------------------------------------------------------------------------------

-- This CONTINUE HANDLER is an equivalent to CPF0000. It absorbs the --

-- hard crashes in SQL, so that they can be handled with a monitor of --

-- SQLCODE instead. When bad data comes in, it is re-routed to CM999. --

-- A record of what has been done in this procedure will be recorded in --

-- CM997. --

--------------------------------------------------------------------------------

--XXXXXXX

-- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION GET DIAGNOSTICS EXCEPTION 1

-- SQLERRMC = MESSAGE_TEXT ;

--XXXXXXX

-- note: in this program, this text was commented out because it was simply

-- not necessary. The data is simple, there are no unexpected cases.

--------------------------------------------------------------------------------

--CLEAR FILE IN ADVANCE

DELETE FROM IMP_MRP4P ;

UPDATE EPBWIPMC2/IMP_MRP4R SET TEXT_2 = TEXT_1 WHERE TEXT_2 = ' ' ;

DELETE FROM EPBWIPMC2/IMP_MRP4R WHERE MATERIAL_NUMBER IS NULL ;

DELETE FROM EPBWIPMC2/IMP_MRP4R WHERE TEXT_1 IS NULL ;

DELETE FROM EPBWIPMC2/IMP_MRP4R WHERE TEXT_2 IS NULL ;

--------------------------------------------------------------------------------

-- SET VARIABLES FOR PROCESSING

--------------------------------------------------------------------------------

OPEN CURSOR_01 ;

/*****************************************************************************/

/* Process Each new Record with the following action: */

/* 1 - Parse each column, looking for "~" as a string separator */

/* 2 - Assemble a tag and tag text in a new variable */

/* 3 - For each variable assembled, write a new row in FPARSED165 */

/* Process Each new Record with the following action: */

/*****************************************************************************/

--------------------------------------------------------------------------------

-- MAIN DATA INTAKE LOOP

--------------------------------------------------------------------------------

SET END_OF_FILE = 0 ;

SET SQLCODE = 0 ;

SET SQLSTATE = '00000' ;

---------------------------------------------------------------------

WHILE END_OF_FILE = 0 DO

FETCH CURSOR_01 INTO

W_MATERIAL_NUMBER ,

W_TEXT_1 ,

W_TEXT_2 ,

W_NOTES_RRN ;

IF SQLCODE <> 0

THEN

SET END_OF_FILE = 1 ;

END IF ;

---------------------------------------------------------------------

-- Do the first LOCATE to find out if there is any '~' Value in the string:

SET STRING_START = 1 ;

SET STRING_END = 0 ;

SET INNER_LOOP = 1 ;

SET STRING_END = LOCATE( '~' , W_TEXT_2 , 1) ;

-- In case there is ONLY ONE string with no "~" delimiter, the locate

-- keyword will yield a zero. Take the whole string instead.

IF STRING_START = 1 and STRING_END = 0

THEN SET STRING_END = 510 ;

END IF ;

-- Write first row, which will contain a general category descriptor.

---------------------------------------------------------------------

-- Insert new row and look for next string marker.

---------------------------------------------------------------------

WHILE INNER_LOOP > 0

----------------------------------------------------

SET STRING_LENGTH = STRING_END - STRING_START ;

IF STRING_LENGTH > 150

THEN -- LIMIT STRING LENGTH TO 150

SET STRING_LENGTH = 150 ;

END IF ;

INSERT INTO IMP_MRP4P

SELECT

W_MATERIAL_NUMBER ,

STRIP( SUBSTRING( W_TEXT_2 , STRING_START, STRING_LENGTH ) )

FROM SYSIBM/SYSDUMMY1 ;

----------------------------------------------------

SET STRING_START = STRING_START + STRING_LENGTH +1 ;

SET STRING_END = LOCATE( '~' , W_TEXT_2 , STRING_START ) ;

IF STRING_END <= 1

THEN SET INNER_LOOP = -1 ;

END IF ;

END WHILE ;

---------------------------------------------------------------------

END WHILE ;

CLOSE CURSOR_01 ;

-- Before letting the file go, run a quick duplicate cleaner to ensure

-- no description is loaded twice by accident.

DELETE FROM IMP_MRP4S H1 WHERE RRN(H1) <

( SELECT MAX( RRN(H2) ) FROM IMP_MRP4S H2

WHERE H1.MATERIAL_NUMBER = H2.MATERIAL_NUMBER

AND H1.TEXT_1 = H2.TEXT_1 ) ;

END -- End of procedure