RE: NOT LOGIC

Thanks, I will give this a try!

________________________________

From: midrange-l-bounces@xxxxxxxxxxxx on behalf of Peter Dow (ML)
Sent: Tue 1/16/2007 10:40 PM
To: Midrange Systems Technical Discussion
Subject: Re: NOT LOGIC



Hi Shannon,

How about

select
 file1.astate
 file1.acity
from
 File1 exception join
 file2 on
  FILE1.ASTATE = FILE2.XRSTATE and
  FILE1.ACITY = FILE2.XRCIT
group by
 file1.astate
 file1.acity

I think what Chris suggested would actually find a lot of matches:  if you have 
AZ, Phoenix in file1, it would NOT match every record in file2 except the one 
with AZ, Phoenix in it.  The exception join above will give you all the records 
in file1 that *cannot* be joined by state & city (hence the "exception").

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

Chris Payne wrote:

How about something like this:

select
 file1.astate
 file1.acity
from
 File1 join
 file2 on
  FILE1.ASTATE <> FILE2.XRSTATE and
  FILE1.ACITY <> FILE2.XRCIT
group by
 file1.astate
 file1.acity

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Shannon O'Donnell
Sent: Tuesday, January 16, 2007 5:29 PM
To: Midrange Systems Technical Discussion
Subject: NOT LOGIC

Can someone give me an example of how to do the following with SQL on
iSeries? (STRSQL)



I mean, I would like an example of the syntax to accomplish this...





Files:      LIBRARY1/FILE1,    LIBRARY2/FILE2



Fields:   FILE1.ASTATE,            FILE1.ACITY

Fields:   FILE2.XRSTATE,           FILES2.XRCITY





Request: Select the records from FILE1 where FILE1.ASTATE <>
FILE2.XRSTATE and FILE1.ACITY <> FILE2.XRCIT  and display distinct
records only  Grouped by FILE1.ASTATE, FILE1.ACITY

 

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