RE: Need some assistance with an SQL query

Thanks Dan,
I'm still running into an issue try to figure out why there are 8 records
that didn't update, well 5 because three are not in the file, that much I
have determined.

I have two records that exists in RouteMaxL1 with the following values:
mxplan='BTXTW'
mxdiv=01
mxins='F'
mxben='OCP'
mxppo=''
Mxtfrm='V"
mxtdt=0

and a record that has mxtdt=20121015

It does not update either record, skips them completely. I have no idea
why...field lengths are an exact match as is the data, I would expect the
one with the mxtdt-0 to be updated but the one with the date value.

Suggestions?

You should be able to just reverse the logic on the exists predicate as
your update didn't change any of the values used in the subquery
predicates.:

Exec Sql
Select * from RoutMaxl1 as a
Where not exists
(select 1 from xRouteMax as b
where a.mxplan = b.mxplan and
a.mxdiv = b.mxdiv and
a.mxins = b.mxins and
a.mxben = b.mxben and
a.mxppo = b.mxppo and
a.mxtfrm = 'V' and
a.mxtdt = 0);

Note I changed the correlated subquery column list to 1 instead of *.
It'll be a little quicker with no change in logic.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rpglist@xxxxxxxxxxx
Sent: Friday, December 07, 2012 9:22 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Need some assistance with an SQL query

I have two files, one is a work file with 991 records and the other is a
production (Test Environment) file with 22 mil. I am updating the
production records that match to records in the work file.

Here is my sql statement:
Exec Sql
Update RoutMaxl1 as a
Set a.mxUdt = :xDate,
a.mxUtm = :xTime,
a.mxUop = 'CPP989825645KY',
a.mxCpdy = 'Y',
a.mxUty = 'C'
Where exists
(select * from xRouteMax as b
where a.mxplan = b.mxplan and
a.mxdiv = b.mxdiv and
a.mxins = b.mxins and
a.mxben = b.mxben and
a.mxppo = b.mxppo and
a.mxtfrm = 'V' and
a.mxtdt = 0);

The statement runs fine but I only update 980 records, which means that I
have 11 records in table A (xRouteMax) that do not exist in table B.

Suggestion on how to get a list of those records? I just want the 11.





--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.