Mystery SQL Problem ( -- RPG400-L

Possibly an optimizer setting (qqaini?) that retrieves only "first x rows" ...
Have you tried adding the same "order by" clause in the first select?

Paul

On 5 déc. 2016, at 19:00, rpg400-l-request@xxxxxxxxxxxx wrote:

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 (Marvin Radding)
2. Re: A Mystery SQL Problem (Paul Therrien)

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

message: 1
date: Mon, 5 Dec 2016 16:06:36 +0000
from: Marvin Radding <marvin.radding@xxxxxxxxxxxxxxxxxxxxx>
subject: RE: A Mystery SQL Problem

No, all records are deleted in the program and the file is cleared in the CL

that calls the program.

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 DB2iSeries
Sent: Friday, December 02, 2016 3:09 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: Re: A Mystery SQL Problem

MMNYOVRP has 40 records to begin with - before the insert

On Dec 2, 2016, at 12: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.

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

message: 2
date: Mon, 05 Dec 2016 11:15:17 -0500
from: Paul Therrien <paultherrien@xxxxxxxxxxxxxxxxxx>
subject: Re: A Mystery SQL Problem

Well, please let us know when you find the solution.

Other suggestions:

- Is it a library list issue?

- What version of OS are you on? Are you up-to-date of your PTFs?

Paul

On 2016-12-05 11:05, Marvin Radding wrote:

Paul,

No, I delete all records before I process the SQL.

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: Saturday, December 03, 2016 4:26 AM
To: 'RPG programming on the IBM i (AS/400 and iSeries)'

<rpg400-l@xxxxxxxxxxxx>

Subject: RE: A Mystery SQL Problem

This may be too obvious, but were there records in MMNYOVRP before you

started?

Paul

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Marvin

Radding

Sent: Friday, December 2, 2016 5:21 PM
To: 'Rpg400 Rpg400-L' <rpg400-l@xxxxxxxxxxxx>
Subject: A Mystery SQL Problem

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,

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.

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

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