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

The point is, the null is required in this scenario. As far as I understand Maya, this is what he wants:

User has an option to filter the results for SupplierCode. If user selects specific supplier, he wants results only for that supplier. But, if you want to NOT filter the results set for supplier, you pass in a null and you get results NOT filtered.
The same goes for other parameters.

Now, the problem is this way the query does not use indices (I haven't acctually tried, but I guess it doesn't).

IMHO, if you know your tables are not big, this pattern of querying is ok because it is very easy on the client side to pass specific/all parameters. If performance is crucial, then dynamically construct your sql on the client side. It's a mess and very hard to maintain, but you will get use of indices that way.

Sasha