Subject Re: [firebird-support] Re: Possible to write this in a way that indices will be used
Author Doug Chamberlin
Maya Opperman wrote:
> Rick Debay Wrote:>
>>> if D.SuplCde is 'ABC' and ISupplierCode is NULL, result will be
> TRUE??
>> Wrong. Since one has a value and the other is NULL, they are DISTINCT.
>> The NOT would flip that to false.
> OK, so Sasha is right, DISTINCT is unfortunately not going to work.
> Any other suggestions for ((D.SuplCde = :ISupplierCode) or
> (:ISupplierCode is null))
> to be able to use indices?

What if, in your client program, you ensured that the parameter values
was never NULL? That way you could remove the test for null and leave
the one for equality (which I assume would enable the use of indexes).

Perhaps, pre-process the parameter to replace null with some value
guaranteed not to appear in the data so that the equality will never be