This was the prologue to the SET ANSI OFF thread.  When I changed for optimization's sake (because the customer had complained that the original code was very slow), I got 0 results due to the legacy code having SET ANSI OFF.

On 5/11/2021 8:47 PM, MB Software Solutions, LLC wrote:

In case you weren't aware that NOT EMPTY appears to be unable to use Rushmore optimization...

There is a regular index on the order.srep field.  I added the SETs to emulate the environment per legacy code.

The results are why I changed the original author's NOT EMPTY(field1) SQL to field1 <> "" instead:

CLEAR OPEN DATABASE c:\temp\test SHARED SET ANSI OFF SET EXACT OFF SET NEAR ON LOCAL lcOpt as String ? SYS(3054,12,"lcOpt")

SELECT distinct srep FROM test!order WHERE srep <> "" INTO CURSOR crap

#IF .F.     Results:     SELECT distinct srep FROM test!order WHERE srep <> "" INTO CURSOR crap     Using index tag Srep to rushmore optimize table order     Rushmore optimization level for table order: partial #ENDIF

SELECT distinct srep FROM test!order WHERE NOT EMPTY(srep) INTO CURSOR crap

#IF .F.     Results:     SELECT distinct srep FROM test!order WHERE NOT EMPTY(srep) INTO CURSOR crap     Rushmore optimization level for table order: none #ENDIF