Hi,
I have the following two tables:
create table strdet
(costcenterms varchar2(20), // parent
costcenterdet varchar2(20), // child
lev varchar2(1))
create table details_det
(costcenterms varchar2(20),
eppid varchar2(30) ,
purchcontyear0 number(4,1) )
And some data:
insert into strdet values ('1' , '1.1','2')
/
insert into strdet values ('1' , '1.2','2')
/
insert into strdet values ('1.1' , '1.1.1','3')
/
insert into strdet values ('1.1' , '1.1.2','3')
/
insert into strdet values ('1.2' , '1.2.1','3')
/
insert into strdet values ('1.2' , '1.2.2','3')
/
insert into strdet values ('1.2' , '1.2.3','3')
/
insert into strdet values ('1.1.1' , '1.1.1.1','4')
/
insert into strdet values ('1.1.1' , '1.1.1.2','4')
/
insert into strdet values ('1.1.2' , '1.1.2.1','4')
/
insert into strdet values ('1.2.1' , '1.2.1.1','4')
/
insert into strdet values ('1.2.1' , '1.2.1.2','4')
/
COMMIT;
insert into details_det values('1.1.1.1','epp1',10);
insert into details_det values('1.1.1.1','epp2',20);
insert into details_det values('1.1.1.1','epp3',0);
insert into details_det values('1.1.1.2','epp1',0);
insert into details_det values('1.1.2.1','epp2',5);
insert into details_det values('1.1.2.1','epp4',15);
insert into details_det values('1.2.1.1','epp1',65);
insert into details_det values('1.2.1.1','epp2',95);
insert into details_det values('1.2.1.2','epp1',5);
commit;
Using the following hierarchical query , i get what i need...
SQL> select grp,
2 sum(purchcontyear0) , sum(PURCHAPPRREQYEAR0)
3 from (
4 select connect_by_root s.costcenterms grp,
5 d.purchcontyear0 , PURCHAPPRREQYEAR0
6 from strdet s,
7 details_det d
8 where s.costcenterdet=d.costcenterms(+)
9 connect by s.costcenterms = prior s.costcenterdet
10 )
11 group by grp
12 /
GRP SUM(PURCHCONTYEAR0) SUM(PURCHAPPRREQYEAR0)
------------------------------------------------------------ ------------------- ----------------------
1 215 130
1.1.1 30 45
1.1.2 20 40
1.2.1 165 45
1.1 50 85
1.2 165 45
6 rows selected
But ... i want to get the above resultset using a non-hierarchical query.... so as to define a fast refreshable mv....Is there any possible way for this...???
Thanks...
Sim