Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Hierarchical queries and group by -- de-hierarchizing

Hierarchical queries and group by -- de-hierarchizing

From: Honza Pazdziora <adelton_at_fi.muni.cz>
Date: Sun, 19 May 2002 10:13:08 GMT
Message-ID: <slrnaeeulj.1sam.adelton@nemesis.fi.muni.cz>


Hello,

I got stuck with what I thought would be a nice case for using hierarchical queries, especially with 9i's ability to join with other tables. But while I can nicely get expanded tree of the whole tree from root to leaves, I have hard time figuring how to get back the aggregated information whether such a tree of rules was matched by the fact table.

Let's say I have a hierarchical set of rules:

RULES:

id      name                    min_count       min_value	fact_id
1       '-'                     1               2               801
2       '-'                     1               3               800
3       '-'                     1               2               815
4       'at least two, case I'  2               0               null
5       '-'                     1               4               802
6       '-'                     1               2               805
7       '-'                     1               2               855
8       'all of case II'        3               10              null
9       'top rule'              2               0               null

HIERARCHY:
id      child_id
4       1
4       2
4       3
8       5
8       6
8       7
9       8
9       4

It basically tries to describe that the 'top rule' (9) has two children (4 and 8) where both have to be satisfied (min_count = 2 for 9). Rule 4 says that at least two (min_count = 2) of 1, 2, 3 have to be satisfied, while rule 8 says that all (min_count = 3) of 5, 6, 7 have to be satisfied and that the value generated by these children has to be at least 10 (min_value = 10). The leaves are then matched against some fact table, referenced by the fact_id column. In the fact table, the fact is either present or not, and it generates some value.

I can wind the result back by hand in PL/SQL, evaluating first the leaves and matching the values with the leave rules (1, 2, 3, 5, 6, 7), then computing the sum of values and count of matched fact lines by hand and seeing if the rules 4 and 8 matched, and then again getting this information back to the root rule 9.

My question is if this could somehow be evaluated using SQL only. The trouble is how to elevate the results from the leaves level to the level of the middle nodes (4 and 8) and then again how to get these accumulated results to the root.

As I'm sure many people have already done this, URL or some hint (especially if this is feasible in SQL) would be appreciated.

[ Sorry if this post shows twice, my slrn issued an error message upon post. ]

Yours,

-- 
------------------------------------------------------------------------
 Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/
 .project: Perl, mod_perl, DBI, Oracle, auth. WWW servers, XML/XSL, ...
------------------------------------------------------------------------
Received on Sun May 19 2002 - 05:13:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US