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 [email protected] [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 > > ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
