> 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

Hi Alan!

If you use Fb 2.5, I suppose you could try:

for select s.partno,s.morefields.....
from stock s
where s.partno = :locsearch
and s.location is not distinct from coalesce(:loclocation, s.location)
and s.trans_type is not distinct from coalesce(:loctransaction, s.trans_type)

If :loclocation is null, then every record should match (is not distinct from differs from equality comparison by including cases where s.location is null). I don't think there's any need for locsearchtype... If you cannot use is distinct from or coalesce, you should at least move s.partno outside of the 'or' bit.

HTH,
Set