× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.