|Subject||Re: [firebird-support] Possible to write this in a way that indices will be used|
> I have a query written as follows:What about this
> 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?
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.