asked on

SQL/400 Procedure does not loop

I am creating a stored procedure to read a file and write the record to another file (NAMECHG) when the modification date field of the first file is = yesterday's date. The SQL statements do not produce any errors and the procedure (NAMECHG5) is created. When it runs, it appears to be working, however it only reads the Table once and does not loop.
CREATE PROCEDURE NAMECHG5
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE YEST CHAR(8);
DECLARE CYEAR CHAR(4);
DECLARE CMONTH CHAR(2);
DECLARE CDAY CHAR(2);
DECLARE HOSP DEC(3,0);
DECLARE SPID CHAR(20);
DECLARE PREVLN CHAR(35);
DECLARE NEWLN CHAR(35);
DECLARE CHGDATE DEC(8,0);
DECLARE CCHGDATE CHAR(8);
DECLARE CHGBY CHAR(4);
DECLARE YEST2 DATE;
DECLARE PREVFN CHAR(35);
DECLARE PREVMN CHAR(35);
DECLARE NEWFN CHAR(35);
DECLARE NEWMN CHAR(35);
DECLARE MSG CHAR(20);
DECLARE YEST3 DATE;
DECLARE CPNNMDT CHAR(10);
DECLARE YEST4 CHAR(8);
DECLARE YEST5 DEC(20,0);
DECLARE YEST6 CHAR(12);
DECLARE DYEST2 CHAR(10);
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE EOF_COND CHAR(1) DEFAULT '0';
DECLARE CSR1 CURSOR FOR
SELECT PNHSP, PNSPID, PNLSTN,
PNFSTN, PNMIDN, PELSTN, PNLMBY, PEFSTN, PNMIDN, PNNMDT
FROM BADMPNU, BADMPEU
WHERE PNHSP = PEHSP AND PNSPID = PESPID AND PNNMDT <> 0;
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET EOF_COND = '1';
SET YEST2 = CURDATE() - 1 DAY;
DELETE FROM NAMECHG;
OPEN CSR1;
RPT: REPEAT
FETCH NEXT FROM CSR1
INTO HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN, CHGBY, NEWFN, NEWMN,
CHGDATE;
IF DATE((SUBSTR(CHGDATE,5,2) || '/' ||
SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4))) = YEST2 THEN
INSERT INTO NAMECHG VALUES(HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN,
NEWFN, NEWMN, CHGDATE, CHGBY);
ELSE

SET YEST3 = DATE((SUBSTR(CHGDATE,5,2) || '/' ||
SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4)));
INSERT INTO SQLAPPTST VALUES(YEST2, YEST3);
END IF;
UNTIL EOF_COND = '1'
END REPEAT RPT;
CLOSE CSR1;
END;

The write into the SQLAPPTST table is only for debug purposes and I have not removed some of the unused variable declare statements.
Any help that you can give me with the loop problem would be much appreciated.

momi_sabag

hi

what happens if you change the loop from an until loop to a while loop ?
do you enter the loop in the first time ?
did you try to add an intialization of the EOF_COND ?
change EOF_COND to be integer
add before the loop starts
SET EOF_COND = 0
maybe the default clause don't get the job done

Jeanniem

ASKER

I tried all of the above suggestions and I still only get through the loop one time.

Member_2_276102

Jeanniem:

Please elaborate on "...it appears to be working". Does that mean that one row and only one row is inserted into NAMECHG or SQLAPPTST? If a row is inserted, is it correct? I.e., does it have correct values in its columns appropriate for the IF test?

Tom

Jeanniem

ASKER

The first record it reads goes into SQLAPPTST (as it should) and the values are correct - it just stops after the first record.

Member_2_276102

Jeanniem:

That indicates general correctness. So far, so good.

If you run this query:

==> select count(*) FROM BADMPNU, BADMPEU
WHERE PNHSP = PEHSP AND PNSPID = PESPID AND PNNMDT <> 0

...I assume you get a count greater than 1?

Is NAMECHG a true SQL table or is it simply an externally described physical (or logical) file? Does it have keys? Are there any key constraints, such as a logical file with unique keys or other types of database constraints? Is NAMECHG journaled? Are you running within a COMMIT boundary? Can you manually execute INSERT statements into NAMECHG to add one or more of the rows you expect? (Executing a couple INSERTs manually would demonstrate that there are no constraints outside of your procedure that might not be easily apparent.)

Tom

Lowfatspread

DELETE FROM NAMECHG

is this the problem...
was namechg empty prior to running?
if so your continue handler has set eof to '1'

you need to make your continue handler more robust (and your code)
by having variable to indicate the position of the events that the handlers are dealing with...

e.g.
Declare CodePos char(30);
...
DECLARE CONTINUE HANDLER FOR NOT_FOUND
if codepos = 'Loop'
SET EOF_COND = '1';
end if;
...
set codepos = 'Delete';
delete from namechg

set codepos = 'loop';
repeat
...

Member_2_2484401

Hmm, that's weird. The following simple code works for me. What are you doing differently?

create procedure dford.LoopCurso2
language sql
set option dbgview = *source
Begin
DECLARE AtEnd integer;
Declare empName varchar(30);
DECLARE x CURSOR for select firstName from dford.employee;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
set AtEnd = 1;
Open x;
fetch_loop: repeat
fetch x into empName;
until (AtEnd = 1)
end repeat fetch_loop;
End

Jeanniem

ASKER

Hi Tom - Thanks for your help. I did what you recommended and got well over 150000 records read in the select count statement. The namechg table is a true sql table (created with sql), does not have any keys, and I can execute Insert commands manually into the file and the inserted values are there.

Hi Lowfatspread - I am working on your recommendations but since I am a novice with SQL/400 I have not been able to get the above changes to compile. Thanks for your input.

Jeanniem

ASKER

Hi Daveslash - Thanks for your example - I will try to look at it and compare it to mine to see how mine differs.

Lowfatspread

--
-- this is your procedure with my basic changes ...
--
CREATE PROCEDURE NAMECHG5
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE YEST CHAR(8);
DECLARE CYEAR CHAR(4);
DECLARE CMONTH CHAR(2);
DECLARE CDAY CHAR(2);
DECLARE HOSP DEC(3,0);
DECLARE SPID CHAR(20);
DECLARE PREVLN CHAR(35);
DECLARE NEWLN CHAR(35);
DECLARE CHGDATE DEC(8,0);
DECLARE CCHGDATE CHAR(8);
DECLARE CHGBY CHAR(4);
DECLARE YEST2 DATE;
DECLARE PREVFN CHAR(35);
DECLARE PREVMN CHAR(35);
DECLARE NEWFN CHAR(35);
DECLARE NEWMN CHAR(35);
DECLARE MSG CHAR(20);
DECLARE YEST3 DATE;
DECLARE CPNNMDT CHAR(10);
DECLARE YEST4 CHAR(8);
DECLARE YEST5 DEC(20,0);
DECLARE YEST6 CHAR(12);
DECLARE DYEST2 CHAR(10);
DECLARE EOF_COND CHAR(1) DEFAULT '0';
declare proc_pos char(30);

DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR NOT_FOUND
begin
if proc_pos='LOOP'
SET EOF_COND = '1';
end if
end

DECLARE CSR1 CURSOR FOR
SELECT PNHSP, PNSPID, PNLSTN,
PNFSTN, PNMIDN, PELSTN, PNLMBY, PEFSTN, PNMIDN, PNNMDT
FROM BADMPNU, BADMPEU
WHERE PNHSP = PEHSP
AND PNSPID = PESPID
AND PNNMDT <> 0;

Set proc_pos='START';

SET YEST2 = CURDATE() - 1 DAY;

Set Proc_pos='DELETE'
DELETE FROM NAMECHG;

OPEN CSR1;

Set Proc_Pos='LOOP';

RPT: REPEAT

FETCH NEXT FROM CSR1
INTO HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN, CHGBY, NEWFN, NEWMN,
CHGDATE;

IF DATE((SUBSTR(CHGDATE,5,2) || '/' ||
SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4))) = YEST2 THEN
INSERT INTO NAMECHG VALUES(HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN,
NEWFN, NEWMN, CHGDATE, CHGBY);
ELSE

SET YEST3 = DATE((SUBSTR(CHGDATE,5,2) || '/' ||
SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4)));
INSERT INTO SQLAPPTST VALUES(YEST2, YEST3);
END IF;
UNTIL EOF_COND = '1'
END REPEAT RPT;
CLOSE CSR1;
END;

--
-- however I believe that all you really need in your procedure is this...
--

CREATE PROCEDURE NAMECHG5
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
Delete from Namechg;

INSERT INTO NAMECHG
SELECT PNHSP, PNSPID, PNLSTN,
PNFSTN, PNMIDN, PELSTN, PELFSTN,PNMIDN,PNNMDT, PNLMBY
FROM BADMPNU, BADMPEU
WHERE PNHSP = PEHSP
AND PNSPID = PESPID
AND PNNMDT <> 0
AND DATE((SUBSTR(CHGDATE,5,2) || '/' ||
SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4))) = curdate() - 1 DAY
;
END;

Jeanniem

ASKER

No go - sorry - I got the first error messages on the code before the -- however and the second trying it with the abreviated code.

MSG ID SEV RECORD TEXT
SQL0104 30 38 Position 9 Token CSR1 was not valid. Valid tokens:
GLOBAL.
Message Summary
Total Info Warning Error Severe Terminal
1 0 0 0 1 0
30 level severity errors found in source
* * * * * E N D O F L I S T I N G * * * * *

SQL0312 30 7 Position 2 Variable PELFSTN not defined or not usable.
SQL0312 30 7 Position 2 Variable CHGDATE not defined or not usable.
SQL0312 30 7 Position 2 Variable CHGDATE not defined or not usable.
SQL0312 30 7 Position 2 Variable CHGDATE not defined or not usable.
Message Summary

Lowfatspread

sorry missed a semicolon on the end if in the handler...
--
-- this is your procedure with my basic changes ...
--
CREATE PROCEDURE NAMECHG5
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE YEST CHAR(8);
DECLARE CYEAR CHAR(4);
DECLARE CMONTH CHAR(2);
DECLARE CDAY CHAR(2);
DECLARE HOSP DEC(3,0);
DECLARE SPID CHAR(20);
DECLARE PREVLN CHAR(35);
DECLARE NEWLN CHAR(35);
DECLARE CHGDATE DEC(8,0);
DECLARE CCHGDATE CHAR(8);
DECLARE CHGBY CHAR(4);
DECLARE YEST2 DATE;
DECLARE PREVFN CHAR(35);
DECLARE PREVMN CHAR(35);
DECLARE NEWFN CHAR(35);
DECLARE NEWMN CHAR(35);
DECLARE MSG CHAR(20);
DECLARE YEST3 DATE;
DECLARE CPNNMDT CHAR(10);
DECLARE YEST4 CHAR(8);
DECLARE YEST5 DEC(20,0);
DECLARE YEST6 CHAR(12);
DECLARE DYEST2 CHAR(10);
DECLARE EOF_COND CHAR(1) DEFAULT '0';
declare proc_pos char(30);

DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR NOT_FOUND
begin
if proc_pos='LOOP'
SET EOF_COND = '1';
end if;
end

DECLARE CSR1 CURSOR FOR
SELECT PNHSP, PNSPID, PNLSTN,
PNFSTN, PNMIDN, PELSTN, PNLMBY, PEFSTN, PNMIDN, PNNMDT
FROM BADMPNU, BADMPEU
WHERE PNHSP = PEHSP
AND PNSPID = PESPID
AND PNNMDT <> 0;

Set proc_pos='START';

SET YEST2 = CURDATE() - 1 DAY;

Set Proc_pos='DELETE'
DELETE FROM NAMECHG;

OPEN CSR1;

Set Proc_Pos='LOOP';

RPT: REPEAT

FETCH NEXT FROM CSR1
INTO HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN, CHGBY, NEWFN, NEWMN,
CHGDATE;

IF DATE((SUBSTR(CHGDATE,5,2) || '/' ||
SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4))) = YEST2 THEN
INSERT INTO NAMECHG VALUES(HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN,
NEWFN, NEWMN, CHGDATE, CHGBY);
ELSE

SET YEST3 = DATE((SUBSTR(CHGDATE,5,2) || '/' ||
SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4)));
INSERT INTO SQLAPPTST VALUES(YEST2, YEST3);
END IF;
UNTIL EOF_COND = '1'
END REPEAT RPT;
CLOSE CSR1;
END;

-- and forgot to change the date column name

--
-- however I believe that all you really need in your procedure is this...
--

CREATE PROCEDURE NAMECHG5
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
Delete from Namechg;

INSERT INTO NAMECHG
SELECT PNHSP, PNSPID, PNLSTN,
PNFSTN, PNMIDN, PELSTN, PEFSTN,PNMIDN,PNNMDT, PNLMBY
FROM BADMPNU, BADMPEU
WHERE PNHSP = PEHSP
AND PNSPID = PESPID
AND PNNMDT <> 0
AND DATE((SUBSTR(PNNMDT,5,2) || '/' ||
SUBSTR(PNNMDT,7,2) || '/' || SUBSTR(PNNMDT,1,4))) = curdate() - 1 DAY
;
END;

Jeanniem

ASKER

The above errors occurred after I added the ; at the end of the endif - sorry.

momi_sabag

what errors ?

Jeanniem

ASKER

The errors listed above in the comment from me (Jeanniem) on Date: 02/07/2007 01:52PM PST.

momi_sabag

i think there is a semicolon missing
you have
SET EOF_COND = '1';
end if
end

DECLARE CSR1 CURSOR FOR

fix it to
SET EOF_COND = '1';
end if;
end;

DECLARE CSR1 CURSOR FOR

Jeanniem

ASKER

I got those errors after I added in the semicolon.

momi_sabag

are you sure you added both of them ?
can you post here the command you execute and the output from db2 ?

Jeanniem

ASKER

1 CREATE PROCEDURE NAMECHG5
2 LANGUAGE SQL
3 MODIFIES SQL DATA
4 BEGIN
5 DECLARE YEST CHAR(8);
6 DECLARE CYEAR CHAR(4);
7 DECLARE CMONTH CHAR(2);
8 DECLARE CDAY CHAR(2);
9 DECLARE HOSP DEC(3,0);
10 DECLARE SPID CHAR(20);
11 DECLARE PREVLN CHAR(35);
12 DECLARE NEWLN CHAR(35);
13 DECLARE CHGDATE DEC(8,0);
14 DECLARE CCHGDATE CHAR(8);
15 DECLARE CHGBY CHAR(4);
16 DECLARE YEST2 DATE;
17 DECLARE PREVFN CHAR(35);
18 DECLARE PREVMN CHAR(35);
19 DECLARE NEWFN CHAR(35);
20 DECLARE NEWMN CHAR(35);
21 DECLARE MSG CHAR(20);
22 DECLARE YEST3 DATE;
23 DECLARE CPNNMDT CHAR(10);
24 DECLARE YEST4 CHAR(8);
25 DECLARE YEST5 DEC(20,0);
26 DECLARE YEST6 CHAR(12);
27 DECLARE DYEST2 CHAR(10);
Display Spooled File
File . . . . . : NAMECHG7 Page/Line 2/32
Control . . . . . Columns 1 - 130
Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1....+....2....+....3
28 DECLARE EOF_COND CHAR(1) DEFAULT '0';
29 declare proc_pos char(30);
30 DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
31 DECLARE CONTINUE HANDLER FOR NOT_FOUND
32 begin
33 if proc_pos='LOOP' THEN
34 SET EOF_COND = '1';
35 end if;
36 END;
37
38 DECLARE CSR1 CURSOR FOR
39 SELECT PNHSP, PNSPID, PNLSTN,
40 PNFSTN, PNMIDN, PELSTN, PNLMBY, PEFSTN, PNMIDN, PNNMDT
41 FROM BADMPNU, BADMPEU
42 WHERE PNHSP = PEHSP
43 AND PNSPID = PESPID
44 AND PNNMDT <> 0;
45
46 Set proc_pos='START';
47
48 SET YEST2 = CURDATE() - 1 DAY;
49 Set Proc_pos='DELETE'
50 DELETE FROM NAMECHG;
51
52 OPEN CSR1;
53
54 Set Proc_Pos='LOOP';
55
56 RPT: REPEAT
57
58 FETCH NEXT FROM CSR1
59 INTO HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN, CHGBY, NEWFN, NEWMN,
60 CHGDATE;
61
62
63 IF DATE((SUBSTR(CHGDATE,5,2) || '/' ||
64 SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4))) = YEST2 THEN
65 INSERT INTO NAMECHG VALUES(HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN,
66 NEWFN, NEWMN, CHGDATE, CHGBY);
67 ELSE
68
69 SET YEST3 = DATE((SUBSTR(CHGDATE,5,2) || '/' ||
70 SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4)));
71 INSERT INTO SQLAPPTST VALUES(YEST2, YEST3);
72 END IF;
73 UNTIL EOF_COND = '1'
74 END REPEAT RPT;
75 CLOSE CSR1;
76 END;
* * * * * E N D O F S O U R C E * * * * *
5722SS1 V5R3M0 040528 Run SQL Statements NAMECHG7 02/08/07 09:20:06 Page
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
MSG ID SEV RECORD TEXT
SQL0104 30 38 Position 9 Token CSR1 was not valid. Valid tokens: EXIT
UNDO CONTINUE.
Message Summary
Total Info Warning Error Severe Terminal
1 0 0 0 1 0
30 level severity errors found in source
* * * * * E N D O F L I S T I N G * * * * *

momi_sabag

what happens if you move the declaration of the cursor before the declaration of the handler ?

that is, the code will look like :

30 DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
31 DECLARE CONTINUE HANDLER FOR NOT_FOUND
37
38 DECLARE CSR1 CURSOR FOR
39 SELECT PNHSP, PNSPID, PNLSTN,
40 PNFSTN, PNMIDN, PELSTN, PNLMBY, PEFSTN, PNMIDN, PNNMDT
41 FROM BADMPNU, BADMPEU
42 WHERE PNHSP = PEHSP
43 AND PNSPID = PESPID
44 AND PNNMDT <> 0;

32 begin
33 if proc_pos='LOOP' THEN
34 SET EOF_COND = '1';
35 end if;
36 END;

Member_2_2484401

I believe the Declare Cursor needs to be placed WITHIN the Begin..End. (See my code above for reference.)

-- DaveSlash

Jeanniem

ASKER

I think both of you had the same idea, but I got the almost the same error:
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
MSG ID SEV RECORD TEXT
SQL0104 30 32 Position 9 Token CSR1 was not valid. Valid tokens:
GLOBAL.
Message Summary
Total Info Warning Error Severe Terminal
1 0 0 0 1 0
30 level severity errors found in source
* * * * * E N D O F L I S T I N G * * * * *

ASKER CERTIFIED SOLUTION

Member_2_276102

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeanniem

ASKER

Wow - I just went back to my code that was working for one record but not looping correctly to copy the source and I wanted to make sure that I had the correct source so I reran the procedure and it worked - it looped through the entire file. The source is below - Thanks to everyone who tried to help me. I am going to go back and give points as soon as Idetermine who helped get this thing to finally run. Thanks again everybody! P.S. I have the origional file and the file that works - does anybody know how to find the difference in the two files (in word or something like that??)?

0001.00 CREATE PROCEDURE NAMECHG1
0002.00 LANGUAGE SQL
0003.00 MODIFIES SQL DATA
0004.00 BEGIN
0005.00 DECLARE YEST CHAR(8);
0006.00 DECLARE CYEAR CHAR(4);
0007.00 DECLARE CMONTH CHAR(2);
0008.00 DECLARE CDAY CHAR(2);
0009.00 DECLARE HOSP DEC(3,0);
0010.00 DECLARE SPID CHAR(20);
0011.00 DECLARE PREVLN CHAR(35);
0012.00 DECLARE NEWLN CHAR(35);
0013.00 DECLARE PREVFN CHAR(35);
0014.00 DECLARE PREVMN CHAR(35);
0015.00 DECLARE NEWFN CHAR(35);
0016.00 DECLARE NEWMN CHAR(35);
0017.00 DECLARE CHGDATE DEC(8,0);
0018.00 DECLARE CCHGDATE CHAR(8);
0019.00 DECLARE CHGBY CHAR(4);

0020.00 DECLARE YEST2 DATE;
0021.00 DECLARE MSG CHAR(20);
0022.00 DECLARE YEST3 DATE;
0023.00 DECLARE CPNNMDT CHAR(10);
0024.00 DECLARE YEST4 CHAR(8);
0025.00 DECLARE YEST5 DEC(20,0);
0026.00 DECLARE YEST6 CHAR(12);
0027.00 DECLARE DYEST2 CHAR(10);
0028.00 DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
0029.00 DECLARE EOF_COND CHAR(1) DEFAULT '0';
0030.00 DECLARE CSR1 CURSOR FOR
0031.00 SELECT PNHSP, PNSPID, PNLSTN,
0032.00 PNFSTN, PNMIDN, PELSTN, PNLMBY, PEFSTN, PNMIDN, PNNMDT
0033.00 FROM BADMPNU, BADMPEU
0034.00 WHERE PNHSP = PEHSP AND PNSPID = PESPID AND PNNMDT <> 0;
0035.00 DECLARE CONTINUE HANDLER FOR NOT_FOUND SET EOF_COND = '1';
0036.00 SET YEST2 = CURDATE() - 1 DAY;
0037.00 DELETE FROM NAMECHG;
0038.00 OPEN CSR1;
0039.00 RPT: REPEAT
0040.00 FETCH CSR1
0041.00 INTO HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN, CHGBY, NEWFN, NEWMN,
0042.00 CHGDATE;
0043.00 IF DATE((SUBSTR(CHGDATE,5,2) || '/' ||
0044.00 SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4))) = YEST2 THEN
0045.00 INSERT INTO NAMECHG VALUES(HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN,
0046.00 NEWFN, NEWMN, CHGDATE, CHGBY);
0047.00 ELSE
0048.00 SET MSG = 'YEST2 THEN YEST3 ';
0049.00 SET YEST3 = DATE((SUBSTR(CHGDATE,5,2) || '/' ||
0050.00 SUBSTR(CHGDATE,7,2) || '/' || SUBSTR(CHGDATE,1,4)));
0051.00 INSERT INTO SQLAPPTST VALUES(YEST2, YEST3, MSG);
0052.00 END IF;
0053.00 UNTIL EOF_COND = '1'
0054.00 END REPEAT RPT;
0055.00 CLOSE CSR1;
0056.00 END;

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_276102

Jeanniem:

If you have old and new source in different members, you can use PDM option 54 to compare. WDSC client also has excellent compare facilities.

Lowfatspread's resolution was great -- Since EOF_COND was already '1' from the initial DELETE of an already empty file, it triggered UNTIL... the very first time through.

Tom

Jeanniem

ASKER

Thank you all for all the help. That's good to know about the PDM option to compare source.

Netminder

Reopened to allow a reallocation of points.

Netminder
Site Admin

Jeanniem

ASKER

I accepted the answer, now how do redistribute points?

Jeanniem

ASKER

I want to accept comments and give 400 points to Lowfatspread and 100 to tliotta - they both provided me with the information I needed.