Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newscon02.news.prodigy.com!newscon06.news.prodigy.com!prodigy.net!news-east.rr.com!news.rr.com!news-server.columbus.rr.com!tornado.ohiordc.rr.com.POSTED!53ab2750!not-for-mail From: J M Davitt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040803 X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.misc,comp.databases.theory Subject: Re: Nontrivial problem - please help References: <4fh8g.75140$H71.64015@newssvr13.news.prodigy.com> <5Tw8g.34026$P2.3167@tornado.ohiordc.rr.com> In-Reply-To: <5Tw8g.34026$P2.3167@tornado.ohiordc.rr.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 118 Message-ID: Date: Thu, 11 May 2006 12:01:44 GMT NNTP-Posting-Host: 24.123.195.58 X-Complaints-To: abuse@rr.com X-Trace: tornado.ohiordc.rr.com 1147348904 24.123.195.58 (Thu, 11 May 2006 08:01:44 EDT) NNTP-Posting-Date: Thu, 11 May 2006 08:01:44 EDT Organization: Road Runner High Speed Online http://www.rr.com Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:127298 comp.databases.theory:39434 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 ( ... )