Thanks Svein, an ingenious use of distinct and nulls. It worked but not
fully, so if I add this it does, but only because I pass a value from an
external application and that would be '' as an empty parameter, not
<null>. And you're right, :locsearchtype is not necessary. The database
was a 1.5 version that I've now updated so all this runs fine.

if (loclocation='') then loclocation = null; /* added */
if (loctransaction='') then loctransaction = null; /* added */
begin
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)

Thanks again
Alan

Alan J Davies
Aldis


On 28/08/2014 12:25, Svein Erling Tysvær
svein.erling.tysvaer@... [firebird-support] 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
>
> 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
>
>