summerize accoding to all leves in herarchial table
I have an herarchial table that contain the folowing structure and data:
STRUCTURE_ID NUMBER(5) not null;
CATALOG_ID NUMBER(5) not null;
MASTER_ITEM_ID VARCHAR2(15) not null;
DETAILED_ITEM_ID VARCHAR2(15) not null;
4 3 0 1
4 3 1 35
4 3 35 35000
4 3 35 35010
4 3 0 3
4 3 3 50
4 3 50 50000
4 3 3 51
4 3 51 51000
4 3 51 51200
In different table, I have financial data. Structure and data:
STRUCTURE_ID NUMBER(5) not null;
CATALOG_ID NUMBER(5) not null;
ITEM_ID VARCHAR2(15) not null;
SUM NUMBER(14,4);
1 4 3 35000 -3000000
2 4 3 50000 -56886.25
3 4 3 51000 61600
4 4 3 51200 10800
I want to have a select statement, that use use the connect by prior statement (to gain the herarchial structure), and to use a group by rollup to have totals to all the hirarchial levels.
I tried the next sql statement, but it not summerise all the levels in the herarchy:
select b.master_item_id, a.item_id, sum(a.sum)
from fin_data a, fin_structure b
where b.detailed_item_id =a.item_id (+)
and a.structure_id=4
and a.structure_id=b.structure_id
connect by prior b.detailed_item_id=b.master_item_id
group by rollup (b.master_item_id,item_id);
The result looks like this :
35 35000 -3000000
35 -3000000
50 50000 -56886.25
50 -56886.25
51 51000 61600
51 51200 10800
51 72400
-2984486.25
There are no totals to masters 1 and 3.
What I do wrong?