RE: A Mystery SQL Problem -- RPG400-L

Paul,

The SQL statements are the last statements to be executed before the *INLR = *ON. There are no duplicates in the source file. And I close the source file before the SQL statements are executed, just to be safe.

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 Paul Therrien
Sent: Wednesday, December 07, 2016 3:39 AM
To: 'RPG programming on the IBM i (AS/400 and iSeries)' <rpg400-l@xxxxxxxxxxxx>
Subject: RE: A Mystery SQL Problem

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

--
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