Subject Re: [firebird-support] Possible to write this in a way that indices will be used
Author Ivan Prenosil
> I have a query written as follows:
> select blah..,
> from MyTable D
> where ((D.SuplCde = :ISupplierCode) or (:ISupplierCode is null))
> and ((D.WrhseCde = :IWarehouseCode) or (:IWarehouseCode is null))
> and ((D.StkCde = :IStockCode) or (:IStockCode is null))
> and ((D.IsActve = 'Y') or (:IIncludeInactive = 'Y'))
> Is there any elegant way that I can rewrite this so that it will make
> use of indexes, but without me having repeat vast volumes of code over
> and over?

What about this

where (D.SuplCde BETWEEN :ISupplierCodeMIN AND :ISupplierCodeMAX) and ...

i.e. instead of :ISupplierCode containing normal value or Null,
you either assign the same value to both ISupplierCodeMIN and ISupplierCodeMAX,
or you assign minimal possible value to ISupplierCodeMIN and maximal possible
value to ISupplierCodeMAX.