CONECT BY for two tables with two JOINS

Question

Hi,

I have 3 tables:
- two with hierarchical structures
(like "dimensions" of recursive type of hierarchy);
- one with summing data (like "facts" with X column).

There are here:

1) DIM1 (ID1, PARENT2, NAME1)  
2) DIM2 (ID2, PARENT2, NAME2)  
3) FACTS (ID1, ID2, X)   

Example of DIM1 table:

--        1   0  DIM1  
----      2   1  DIM1-A  
------    3   2  DIM1-A-A  
--------  4   3  DIM1-A-A-A  
--------  5   3  DIM1-A-A-B  
------    6   2  DIM1-A-B  
--------  7   6  DIM1-A-B-A  
--------  8   6  DIM1-A-B-B  
------    9   2  DIM1-A-C  
----     10   1  DIM1-B  
------   11  10  DIM1-B-C  
------   12  10  DIM1-B-D  
----     13   1  DIM1-C  

Example of DIM2 table:

--        1   0  DIM2  
----      2   1  DIM2-A  
------    3   2  DIM2-A-A  
--------  4   3  DIM2-A-A-A  
--------  5   3  DIM2-A-A-B  
--------  6   3  DIM2-A-B-C  
------    7   2  DIM2-A-B  
----      8   1  DIM2-B  
----      9   1  DIM2-C  

Example of FACTS table:

 1   1  100  
 1   2   30  
 1   3  500  
 --    ................  
 13  9  200  

And I would like to create the only SELECT
where I will specify parent for DIM1 (for example ID1=2 for DIM1-A)
and parent for DIM2 (for example ID2=2 for DIM2-A) and
SELECT will generate report like this:

Name_of_1 Name_of_2 Sum_of_X  
--------- --------- ----------  
DIM1-A-A  DIM2-A-A  (some sum)  
DIM1-A-A  DIM2-A-B  (some sum)  
DIM1-A-B  DIM2-A-A  (some sum)  
DIM1-A-B  DIM2-A-B  (some sum)  
DIM1-A-C  DIM2-A-A  (some sum)  
DIM1-A-C  DIM2-A-B  (some sum)  

I would like to use CONNECT BY phrase, START WITH phrase,
SUM phrase, GROUP BY phrase and OUTER or INNER (?) JOIN.
And nothing more extensions of Oracle 10.2.
In other words: only with "classic" SQL and
only Oracle extensions for hierarchy queries.

Is it possible?

I tried some experiments with question in
http://stackoverflow.com/questions/2922167/mixing-together-connect-by-inner-join-and-sum-with-oracle

(where is a very nice solution but only for one
dimension table ("Tasks"), but I need to JOIN two dimension tables
to one facts table), but I was not succesful.

Thank you very much in advance.

Mirek

Answer 1

"Some sum" is not very descriptive, so I don't see why do you need CONNECT BY at all.

SELECT  dim1.name, dim2.name, x
FROM    (
        SELECT  id1, id2, SUM(x) AS x
        FROM    facts
        GROUP BY
                id1, id2
        ) f
JOIN    dim1
ON      dim1.id = f.id1
JOIN    dim2
ON      dim2.id = f.id2

Answer 2

I think what you're trying to do is get the sum of the value in the facts table for all of the children of the specified rows grouped by the topmost children. This would mean that in your example above, the results for the first row would be the sum any intersections of (DIM1-A-A, DIM1-A-A-A, DIM1-A-A-B) and (DIM2-A-A, DIM2-A-A-A, DIM2-A-A-B, DIM3-A-A-C) found in the FACTS table. With that assumption, I have come to the following solution:

SELECT root_name1, root_name2, SUM(X)
FROM ( SELECT CONNECT_BY_ROOT(name1) AS root_name,
             id1
         FROM dim1
      CONNECT BY parent1 = PRIOR id1
      START WITH parent1 = 2) d1
     CROSS JOIN
     ( SELECT CONNECT_BY_ROOT(name2) AS root_name,
              id2
         FROM dim2
      CONNECT BY parent2 = PRIOR id2
      START WITH parent2 = 2) d2
     LEFT OUTER JOIN
     facts
     ON     d1.id1 = facts.id1
        AND d2.id2 = facts.id2
GROUP BY root_name1, root_name2

(This also assumes that the columns of FACTS are named ID1, ID2, and X.)

Answer 3

Hi Allan, than you very, very MUCH!
Your answer-solution is EXACTLY one, which I have founded. Exactly!!

Hi Quassnoi, I apology for my not fully concrete formulation of my need of sum.
I really need the sum of the value in the facts table for all
of the children of the specified rows grouped by the topmost children
(like was exactly understood by Allan),
but my English did not allowed me to formulate it so clearly like Allan did.

And I apology to both of you for my terrible English
and my mistake id definiton of DIM1 table structure:
correct is naturally (ID1, PARENT2, NAME1).

Mirek

Answer 4

Sorry for second case, I very apology!

I repeted the same error as in my initial question. The corrrect structures of my three tables is this:

1) DIM1 (ID1, PARENT1, NAME1)
2) DIM2 (ID2, PARENT2, NAME2)
3) FACTS (ID1, ID2, X)

Mirek