>With settings ENGINEBEHAVIOR =70, vfp6 version sp5, vfp9 version sp2
>joining 2 files
>BACKOUTS
>NOREPORT, where this file contains null values in field encounter_transaction_number_tx in certain rows
>
>This code returns 10 records when run in vfp6 and 0 records when run in vfp9
>select backouts.encounter_tracking_number_tx from backouts where backouts.encounter_tracking_number_tx;
>not in (select noreport.encounter_tracking_number_tx from noreport) into cursor report;
>group by backouts.encounter_tracking_number_tx
>
>This code returns 10 records in BOTH vfp6 and vfp9
>select backouts.encounter_tracking_number_tx from backouts where (backouts.encounter_tracking_number_tx;
>not in (select noreport.encounter_tracking_number_tx from noreport WHERE NOT ISNULL(noreport.encounter_tracking_number_tx))) into cursor report;
>group by backouts.encounter_tracking_number_tx
>
>Is there a configuration option for vfp9 that will make it unnecessary to find and modify my old SQL statements for this condition?
>
>TIA
>Howie

I'm not sure about the setting, but this select is much better optimizable (performs faster) when it's written this way

select encounter_tracking_number_tx from backouts RE ;
LEFT JOIN  noreport NO On RE.encounter_tracking_number_tx = NO.encounter_tracking_number_tx ;
and NO.encounter_tracking_number_tx IS NULL  into cursor report;
group by RE.encounter_tracking_number_tx