RE: Need some assistance with an SQL query

[rpglist@xxxxxxxxxxx]

I didn't quite get it right with the exception join, but thanks
Alan....only problem at this pokint is that query returns just 3 records
LOL


> Off the top of my head try the following
> (select b.* from
> xRouteMax as b
> exception join RoutMaxl1 as a
>       on 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);
>
> Alan Shore
> Programmer/Analyst, Direct Response
> E:AShore@xxxxxxxx
> P:(631) 200-5019
> C:(631) 880-8640
> "If you're going through Hell, keep going" - Winston Churchill
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rpglist@xxxxxxxxxxx
> Sent: Friday, December 07, 2012 10: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.
>
>
> Disclaimer: This message contains confidential information and is intended
> only for the individual named. If you are not the named addressee you
> should not disseminate, distribute or copy this e-mail. Please notify the
> sender immediately by e-mail if you have received this e-mail by mistake
> and delete this e-mail from your system. E-mail transmission cannot be
> guaranteed to be secure or error-free as information could be intercepted,
> corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.
> The sender therefore does not accept liability for any errors or omissions
> in the contents of this message, which arise as a result of e-mail
> transmission. If verification is required please request a hard-copy
> version. Any views or opinions presented are solely those of the author
> and do not necessarily represent those of the company.
> --
> 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.