On Thu, 28 Aug 2014 12:11:59 +0100, "Alan J Davies
Alan.Davies@... [firebird-support]"
<firebird-support@yahoogroups.com> wrote:

> Hi, I'm looking at a system that I have not touched for some years and
> wonder if there is a way to improve this code. This all works, but is
> there a better or more efficient way? I can't think of how to combine
> them into one 'where' statement.
> Basically I look for a part based on increasingly tight conditions.
> /* search conditions
> 0 partno only,
> 1 partno & location
> 2 partno & Trans_type
> 3 partno & location & Trans_type */
>
> input parameters
> locsearch char(20),
> locsearchtype integer,
> loclocation char(1),
> loctransaction char(1)
>
> begin
> for select s.partno,s.morefields.....
> from stock s
> where (:locsearchtype=0
> and s.partno=:locsearch)
> or (:locsearchtype=1
> and s.partno=:locsearch
> and s.location=:loclocation)
> or (:locsearchtype=2
> and s.partno=:locsearch
> and s.trans_type=:loctransaction)
> or (:locsearchtype=3
> and s.partno=:locsearch
> and s.trans_type=:loctransaction
> and s.location=:loclocation)
> into :partno_out,:morefields_out.....
> do
> suspend;
> end

You probably get better performance by dynamically building the statement
and executing it with EXECUTE STATEMENT. It might be less readable, but it
will likely perform better because the optimizer will be able to select a
better plan.

Mark