MySQL Forums
Forum List  »  General

Re: Dynamic filter clause
Posted by: Peter Brawley
Date: September 14, 2015 12:39PM

> didn't work

Didn't return the correct rows? Too many rows? Too few?

Queries like this are easier to debug if you use Join syntax, thus separating join and filtering criteria, eg ...

select t.StockId 
from stock s
join Triggers t using (stockId)
where ( ( (t.minPrice IS NOT NULL and s.Price <= t.minPrice ) 
        or ( t.maxPrice IS NOT NULL and s.Price >= t.maxPrice ) 
      ) 
  and ( (t.minVolume IS NOT NULL and s.Volume <= t.minVolume ) 
        or (t.maxVolume IS NOT NULL and s.volume >= t.maxVolume ) 
      ) 
) ;

To find out which clauses aren't behaving as expected, comment out one or two of the filters at a time. If necessary, add other columns to the Select list to see what the query is finding. If that doesn't reveal the problem, make it a Left Join and again check effects of the filters.

Options: ReplyQuote


Subject
Written By
Posted
September 13, 2015 10:50PM
Re: Dynamic filter clause
September 14, 2015 12:39PM
September 14, 2015 11:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.