Need help with Oracle "Hierarchical Rollup" problem 4

[DeepDiverMom]

I need some advice for using an enhanced "CONNECT BY...START WITH" statement. (I know that Oracle MVP, "Sem" posted an excellent solution many months ago, but Tek-Tips's SEARCH feature is woefully inadequate to find his solution...I've unsuccessfully searched by every key word I can think of.) So, I'm hoping that Sem (or someone else) can help me.

Here is the table from which I'm working:

select * FROM org;
 
        ID NAME           BUDGET  PARENT_ID
---------- ---------- ---------- ----------
         1 Company A
         2 Region 1                       1
         3 Area 1                         2 
         4 Office 1                       3
         5 Dept 1         100000          4
         6 Dept 2         110000          5
         7 Dept 3         105000          6
         8 Office 2                       3 
         9 Dept 1         112000          8
        10 Dept 2         106000          8
        11 Area 2                         2
        12 Office 1                      11
        13 Dept 1         140000         12 
 
13 rows selected.

For anyone interested in helping, here are the "CREATE TABLE..." and "INSERTs" for the above data:

Create table org (id number, name varchar2(10),budget number, parent_id number);
Insert into org values (1,'Company A',null,null);
Insert into org values (2,'Region 1',null,1);
Insert into org values (3,'Area 1',null,2); 
Insert into org values (4,'Office 1',null,3);
Insert into org values (5,'Dept 1',100000,4);
Insert into org values (6,'Dept 2',110000,4);
Insert into org values (7,'Dept 3',105000,4);
Insert into org values (8,'Office 2',null,3); 
Insert into org values (9,'Dept 1',112000,8);
Insert into org values (10,'Dept 2',106000,8);
Insert into org values (11,'Area 2',null,2);
Insert into org values (12,'Office 1',null,11);
Insert into org values (13,'Dept 1',140000,12);

Here is a sample set of SQL-query code that gets me part-way to my solution:

col a heading "Unit" format a20
col b heading "Budget" format $999,999
select lpad(' ',(level-1) * 3) ||name a, budget b
from org
start with parent_id is null
connect by prior id = parent_id; 
 
Unit                    Budget
-------------------- ---------
Company A
   Region 1
      Area 1
         Office 1
            Dept 1    $100,000
            Dept 2    $110,000
            Dept 3    $105,000 
         Office 2
            Dept 1    $112,000
            Dept 2    $106,000
      Area 2
         Office 1
            Dept 1    $140,000

13 rows selected.

But here is the output that I need, resulting from a roll-up of child...grandchild...great-grandchild data:

Company A                 $673,000 <--- Roll up of its 1 Region
    Region 1              $673,000 <--- Roll up of its 2 Areas
        Area 1            $533,000 <--- Roll up of its 2 Offices
            Office 1      $315,000 <--- Roll up of its 3 Depts
                Dept 1    $100,000
                Dept 2    $110,000
                Dept 3    $105,000
            Office 2      $218,000 <--- Roll up of its 2 Depts
                Dept 1    $112,000
                Dept 2    $106,000
        Area 2            $140,000 <--- Roll up of its 1 Office
            Office 1      $140,000 <--- Roll up of its 1 Dept
                Dept 1    $140,000

Thanks in advance for any help you can provide.

 

[sem]

I'm not sure whether you still need it (sorry, I was on leave for all last month) as well as I'm not sure whether I've ever answered such question, but here's my idea:

SELECT lpad(' ', (LEVEL - 1) * 3) || NAME a,
       budget b,
       (SELECT SUM(budget)
          FROM org o2
         START WITH o2.id = o1.id
        CONNECT BY PRIOR id = parent_id)
  FROM org o1
 START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id

Regards, Dima

http://seminihin.narod.ru

 

[DeepDiverMom]

Dima,

You are such a brainiak!!! I tired your solution (without any changes) and it worked perfectly!!! You are wonderful to post this solution.

I am so happy you are back from your leave of absence. We will all benefit from your return.


Mom

 

[carp]

When I saw this posted, I thought "Dima would rip out a solution to this in a heartbeat!". So nice to have you back!

 

[SantaMufasa]

Dima,

One of those Purple Stars up there is from me (from yesterday) for your excellent solution...I just didn't have time until now to textually aknowledge the cleverness of your work. If I could give multiple stars to you, I would.

Yes, great to have you back from your well-deserved vacation to Crimea.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Thank you all, but I can't get any reason for this star-shower from such obviously inefficient piece of code. I still hope that somebody would send a better one with using analytic one-way functions.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Dima,

You are too humble...Remember, we cannot improve on the efficiency of a piece of code until someone proposes (even an inefficient) piece of code. Personally, I think your code is great! You would need to prove to me that it is inefficient. Also, remember that inefficient is a comparative term...it is only inefficient if someone proposes working, alternate code that is more efficient.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Dave, it's inefficient because of embedded hierarchical subquery query that has to be executed many times. Note, that it's executed against the whole table data, not against (sufficient!) data selected by the main query.
So the aim is to calculate aggregates on that potentially much smaller set. In the case of large table and relatively small expected result pl/sql would be far more efficient (place temporary results to some collection and then just loop through it only 1 time in reverse order).
Thus I hope that a solution exists that utilizes analytic functions like LEAD or analytic SUM capable to keep temporary results. Unforunately I'm not quite strong in this area, so can neither provide such solution nor prove that it doesn't exist.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Remember, Dima, "In the Land of the Blind, the One-Eyed Man is King." So, thanks King Dima for your post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

While I understand your concern about the efficiency of your solution, I can't avoid comparing it to the efficiency of a problem with no solution. Yours is FAR superior!