Re: Nontrivial problem - please help

"Gary Greenberg" <gary.greenberg_at_sbcglobal.net> wrote in message news: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.

Why ?

> 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).

You mixed attribute names , operators and data. I think you need to disentangle them somehow. Oracle has some feature to work with expressions stored as data, but I have not tested it.

> 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.

Write a stored procedure if you cannot do it in one query then. Received on Wed May 10 2006 - 15:18:45 CEST