RE: SQL HELP

This can also be done with an exception join which may be optimized
better (meaning it will run faster) than the sub query method shown
below. The general format of that is:

select file1.acctno from file1 
exception join file2 on file1.acctno = file2. acctno

You can also do this with a left outer join like this:

select file1.acctno from file1 
left outer join file2 on file1.acctno = file2. acctno
where file2.acctno is null

I find the exception join to be more clear than the left outer join but
they are the same functionally. All of this syntax is described in the
database manuals but those are not all that great for learning SQL from
scratch. There are better books for that. I think you will find some
listed if you search the archives.

Also, if you find that the query takes a long time to run (or you are
just curious to see what it's deciding to do under the covers), enter
STRDBG UPDPROD(*YES) on a command line before running the query in
STRSQL. When it completes, look at your job log and you'll see the
debugging messages from the query optimizer. If the query takes a long
time to run (or kills the system while it's running), use the CHGQRYA
command to set the max estimated time to zero. This will cause the query
optimizer to spit out the debug message but the query will not actually
run.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Buck
Sent: Wednesday, March 14, 2007 5:11 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQL HELP

I only want to select records that have
no matching number in subac00001. 

The general form is

select ... from ...
  where column not in
  (select subac00001 from table
     where...)

Basically, the inner select creates a list of subac00001 and the outer 
select checks to see that your key isn't in that list.
   --buck