Dynamic filter clause

[Manish Gurnani]

I have a 2 tables with sample data as below:

Table Stock :

StockId,Price,Volume

1,100.0,1000O

2,200.0,20000

Table Triggers :

StockId, minPrice, maxPrice, minVolume, maxVolume

1,80.0,90.0,null,11000

1,null,90.0,null,null

2,null,250,0,null,null

Basically Triggers is used to store filter criteria. A matching criteria would be whenever Price is below minPrice or above maxPrice. Similarly for Volume. Any Null values are to be ignored. Stock table has unique StockIds while Triggers could have duplicates.

I want to retrieve rows from Triggers whenever ALL criteria are met for a stock. eg: in given data, row 2 of Triggers would match (100 > 90 and no volume criteria)..

I tried using query like this but it didn't work:

select t.StockId from Triggers t, Stock s where t.stockId = s.stockId and
(((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))) ;

What would be the best way to get this done?