Re: NOT LOGIC

["Peter Dow (ML)" <maillist@xxxxxxxxxxxxxxx>]

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