Nontrivial problem - please help

From: Gary Greenberg <gary.greenberg_at_sbcglobal.net>
Date: Wed, 10 May 2006 08:11:44 GMT
Message-ID: <4fh8g.75140$H71.64015_at_newssvr13.news.prodigy.com>

I am trying to solve a little (but nasty) problem and I'll appreciate if someone can give me an advice:
In the database I have two tables: COMPONENTS and RULES. Beside id COMPONENTS has 4 columns corresponding to certain properties. RULES table has columns:
1.SOURCE - char(1) which can take values 'A', 'B', 'C' and 'D' corresponding to component property.
2.COMPARATOR - char(2) which stores values like 'EQ','GT','GE','SW',etc.

• 12 possible values total. 3.CRITERIA_VALUE - varchar(128)

My goal is by given component Id find rules it satisfies. For example if component has PROPERTY_B value equal to 'ABCDEFG' it should find the row in the RULES table which has SOURCE='B', COMPARATOR='SW' and CRITERIA_VALUE='ABC' (SW - stands for start with).

I am trying to construct such query using CASE clauses and DECODE function but can't make the whole thing. It is becoming enormous with multi-layered subqueries and it won't even compile. I can certainly do it piecemeal and finish processing in the client code but it would impact performance as multiple queries will be sending data back and forth.
If someone have an idea how it can be accomplished in one query or in a stored procedure, I'd like to hear it.
Thanks a lot. Received on Wed May 10 2006 - 10:11:44 CEST