You seem to imply that there is some program logic occurring aside from just
the sql statements you are showing here. This does not sound like an SQL
issue but a program logic issue.
Paul
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Marvin
Radding
Sent: Tuesday, December 6, 2016 5:41 PM
To: 'rpg400-l@xxxxxxxxxxxx' <rpg400-l@xxxxxxxxxxxx>
Subject: RE: A Mystery SQL Problem
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
