It might be some sort of write blocking? How are you noticing these duplicate records?
Are there still duplicates left when the job is finished?
On Tue, Dec 6, 2016 at 11:41 PM +0100, "Marvin Radding" <marvin.radding@xxxxxxxxxxxxxxxxxxxxx<mailto:marvin.radding@xxxxxxxxxxxxxxxxxxxxx>> wrote:
This program is use to build records for the AHPOVRP file. When exiting the program, the records, identified by the effective date, are copied to the MMNYOVRP file. When they do this on a state by state basis, the duplicates are always at the end and this will move as more states are built. For example, when they build the first state, it duplicated 40 records. When they built the second state the duplicates for the first state disappeared and the second state had duplicates. And when they built the third state there were no duplicates on the first two states but then the third state had duplicates. Each time, the program will extract from the AHPOVRP file all the records built up to that time and will only crate duplicates for the last few records. I am very mystified.
Thanks,
Marvin
No trees were killed in the sending of this message, but a large number of electrons were terribly inconvenienced
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Buck Calabro
Sent: Tuesday, December 06, 2016 2:30 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: A Mystery SQL Problem
On 12/2/2016 5:20 PM, Marvin Radding wrote:
exec sql
(SELECT count(*) into :count
FROM ahpovrp
WHERE ROEFDT = :effective_year);
exec sql
INSERT INTO MMNYOVRP
(SELECT * FROM ahpovrp
WHERE ROEFDT = :effective_year
ORDER BY rocomp, rostcd, roplan, robenp);
When I execute the first SQL statement, COUNT is 100; but when the second statement the file MMNYOVRP contains 140 records.
I don't understand how this can be. Any ideas?
It's not clear to me either. As a diagnostic, what do the 'extra' 40 rows look like? Can you do an exception join between MMNY and AHP and find out what the deltas are? That may shed some light...
--
--buck
Visit wiki.midrange.com and register for an account. Edit a page that helps you, and because it's public, you'll help someone else, too!
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
Notice: This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended exclusively for the person(s) to whom it is addressed. This message may also contain Protected Health Information (PHI) and must be treated confidentially and handled in accordance with HIPAA and other federal and state privacy laws. Any use, copying, retention or disclosure by any person other than the intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended recipient or their designee, please notify the sender immediately and delete this e-mail (and any accompanying attachments).
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
