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.

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.

You could read the whole RULES table and compile an SQL statement from
that. Alternatively maybe you can create a function that does the
matching and use that in your query.

Cheers

robert

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.

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

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