Re: Nontrivial problem - please help

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Thu, 11 May 2006 12:01:44 GMT
Message-ID: <IIF8g.23774$YI5.11420_at_tornado.ohiordc.rr.com>

J M Davitt wrote:
> 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.

>
>
> This could be...
> [ ] fun
> [ ] cumbrous
> [ ] instructive
> [ ] all of the above
> [ ] none of the above.
>
> Part of the column name is in the data, is that right?
> And we don't really have fragments of SQL in the database;
> instead there are codes that indicate how to construct
> an SQL condition using that datum that names part of a
> column and a constant?
>
> Okay. Using what I had on hand I scribbled some stuff:
>
> select * from rules ;
> source | comparator | criterion
> --------+------------+-----------
> A | EQ | a
> B | NE | b
> C | SW | c
> D | EW | d
> (4 rows)
>
> select * from components ;
> id | propertya | propertyb | propertyc | propertyd
> ------+-----------+-----------+-----------+-----------
> junk | a | B | c here | here d
> (1 row)
>
> select * from match('junk') ;
> result
> ---------------------------------------------------------------
> junk {'a', 'B', 'c here', 'here d'} matched propertyA EQ 'a'
> junk {'a', 'B', 'c here', 'here d'} matched propertyB NE 'b'
> junk {'a', 'B', 'c here', 'here d'} matched propertyC SW 'c'
> junk {'a', 'B', 'c here', 'here d'} matched propertyD EW 'd'
> (4 rows)
>
> The result in the last column is a string composed from the
> data lying about; I made up 'EW' for 'end with.'
>
> Of course, this ain't Oracle -- and if you could see the way
> Postgres make you quote quotes, you would be *very* glad that
> you're using Oracle. (Other Postgres features kick The Big O's
> ass, but declaring "stored procedures" ain't one of 'em!)
>
> An Oracle table function is probably the best way to go...
>
>
> [Back to the quiz...]
>
> [ x ] fun -- because I like this crap
> [ x ] cumberous -- because the design mixes design and data
> [ x ] instructive -- stop doing that!
>
>
> This tickled my interest because there are some folks at my
> workplace running around proposing just such a solution. Did
> someone recently publish a technique like this on the web?
>
> AAR: let me know if you're up to a transliteration...

I've thought of another way to do it that doesn't involve an Oracle table function, I don't have a database with me (well, actually, it's on a different machine) so it might not work as coded...

Use Oracle's "query factoring" to make four rows out of each component

with single_property as (
select id as id

,      'A' as target
,      propertyA as value

from components
union all
select id as id

,      'B' as target
,      propertyB as value

from components
... )
select id
from single_property
join rules on (source = target)
where ( comparator = 'EQ' and value = criterion )

    or ( comparator = 'NE' and value <> criterion )     or ( comparator = 'SW' and substr(value,1,length(criterion)) = criterion)

    or ( ... ) Received on Thu May 11 2006 - 14:01:44 CEST