>>Tried that too. What this does, though, is filters
>>out records from the input file that don't have the
>>120 code. I need all of the records from the input
>>file, and just the matching link and code from the
>>error file. The where zaps any records that aren't
>>120's.
>
>Keith,
>
>So why do you have the "120" in the select at all? The first one sure looked like you only wanted error 120'as in the result. What is the goal? If it is to match the items from table1 to table2 and incldue all of table1, then this should work fine;

Because I need all of the DataPPO records, but only the dmlError
records that have 120 codes (not any other codes).

DataPPO is an ancillary table that ties into a
main table that has already been processed.
I need to do some special processing on the
records in DataPPO that have records in the
main table that are duplicates.

DataPPO might look like this:

Name   Fed_TaxId   Tax_Sufx
Jim    111111111   0000
Keith  222222222   0000    
K      333333333   0000    
Bill   444444444   0001

dmoPPO might look like this:

Name   Fed_TaxId   Tax_Sufx   dm_Link   Code
Jim    111111111   0000
Keith  222222222   0000       1         120
K      333333333   0000       1         120
Bill   444444444   0001

Again, though, my main concern was that I'm
not able to get any optimization off my
Fed_TaxId and Tax_Sufx indexes.

I snipped the code sample you had in here, but
basically when I've tried it as you specified,
it not only gives me one DataPPO recrod for
each error type, it also yeilds no optimization.
Hence my problem...

>
>Make sure there is an index on each table for Fed_taxid + tax_sufx for Rushmore.

Both tables are indexed as follows

INDEX ON Fed_TaxId TAG Fed_TaxId
INDEX ON Tax_Sufx TAG Tax_Sufx

and a MODI STRU shows both indexes there,
unfiltered, non-unique.

Are you suggesting that thin index should be
on Fed_TaxId + Tax_Sufx???

As I mentioned in another note, it seems like
a classic optimization case. Obviously I'm
missing sumpin'.

...kt