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