RE: A Mystery SQL Problem -- RPG400-L

How many rows do you have in MMNYOVRP where the date matches your effective year?

Adrian

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rpg400-l-request@xxxxxxxxxxxx
Sent: 07 December 2016 15:26
To: rpg400-l@xxxxxxxxxxxx
Subject: RPG400-L Digest, Vol 15, Issue 550

Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific than "Re: Contents of RPG400-L digest..."

*** NOTE: When replying to this digest message, PLEASE remove all text unrelated to your reply and change the subject line so it is meaningful.

Today's Topics:

1. RE: A Mystery SQL Problem (Joni V.)
2. RE: A Mystery SQL Problem (Paul Therrien)
3. Re: A Mystery SQL Problem (Charles Wilt)
4. Re: A Mystery SQL Problem (Michael Ryan)
5. Re: A Mystery SQL Problem (Raul A Jager W)

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

message: 1
date: Wed, 7 Dec 2016 07:55:54 +0000
from: "Joni V." <joni_vanderheijden@xxxxxxxxxxx>
subject: RE: A Mystery SQL Problem

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

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

message: 2
date: Wed, 7 Dec 2016 06:39:06 -0500
from: "Paul Therrien" <paultherrien@xxxxxxxxxxxxxxxxxx>
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?

I have a real mystery. I am having problems with an SQL INSERT.

I am performing maintenance on a file and then extracting from that
file and inserting the new records into another file. Here is the code:

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?

Commitment control is off. The file I extract from is closed at this point.

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

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

message: 4
date: Wed, 7 Dec 2016 09:10:02 -0500
from: Michael Ryan <michaelrtr@xxxxxxxxx>
subject: Re: A Mystery SQL Problem

Examine variable SQLER3 right after the INSERT and see how many records were inserted.

On Wed, Dec 7, 2016 at 9:02 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

Is this the actual code? Or a cut down version?

Assuming cut down, what happens between the count and the insert?

What process and when are the records added to ahpovrp?

Charles

On Fri, Dec 2, 2016 at 3:20 PM, Marvin Radding <
marvin.radding@xxxxxxxxxxxxxxxxxxxxx> wrote:

I have a real mystery. I am having problems with an SQL INSERT.

I am performing maintenance on a file and then extracting from that
file and inserting the new records into another file. Here is the code:

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?

Commitment control is off. The file I extract from is closed at this

point.

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

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

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

message: 5
date: Wed, 7 Dec 2016 12:25:57 -0300
from: Raul A Jager W <raul@xxxxxxxxxx>
subject: Re: A Mystery SQL Problem

Did you check the journal?

On 12/07/2016 11:02 AM, Charles Wilt wrote:

Is this the actual code? Or a cut down version?

Assuming cut down, what happens between the count and the insert?

What process and when are the records added to ahpovrp?

Charles

On Fri, Dec 2, 2016 at 3:20 PM, Marvin Radding <
marvin.radding@xxxxxxxxxxxxxxxxxxxxx> wrote:

I have a real mystery. I am having problems with an SQL INSERT.

I am performing maintenance on a file and then extracting from that
file and inserting the new records into another file. Here is the code:

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?

Commitment control is off. The file I extract from is closed at this point.

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

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --

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

Subject: Digest Footer

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) digest 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

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

End of RPG400-L Digest, Vol 15, Issue 550
*****************************************

This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk
-------------------------------------------------------------------------------------------------------------------------------------------------
The Medical Protection Society Limited (MPS) is a company limited by guarantee. Registered in England No. 36142 at 33 Cavendish Square, London, W1G
0PS. VAT number 524 251475. MPS is not an insurance company. All the benefits of membership of MPS are discretionary as set out in the Memorandum
and Articles of Association. This communication and any files transmitted with it are confidential and may be legally privileged. They are intended
solely for the addressee, and access by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or
use of any kind or description, or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. If you have received
this email in error, please contact the sender and then delete from your system. MPS cannot accept any responsibility for the accuracy or completeness
of this message as it has been transmitted over a public network. If you suspect that this message may have been intercepted or amended, please contact
the sender. If verification of this email is required please request a hard-copy version from the sender.

This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk