Re: Nontrivial problem - please help

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Thu, 11 May 2006 01:58:25 GMT
Message-ID: <5Tw8g.34026$P2.3167@tornado.ohiordc.rr.com>

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... Received on Wed May 10 2006 - 20:58:25 CDT