Re: Nontrivial problem - please help

Gary Greenberg wrote:
> 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.

I can't visualise a single query succeeding (unless perhaps with UNION ALL) - but with a stored proc it seems quite feasible using a temporary table.

Cheers, Frank. Received on Wed May 10 2006 - 15:13:23 CEST