Home » SQL & PL/SQL » SQL & PL/SQL » Another way to write hierarchical query...??? (Db10g v.2 on XP platform)
Another way to write hierarchical query...??? [message #349399] Sun, 21 September 2008 07:29 Go to next message
skoskos
Messages: 19
Registered: November 2005
Location: Hell(as)
Junior Member
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
Re: Another way to write hierarchical query...??? [message #349413 is a reply to message #349399] Sun, 21 September 2008 11:17 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Sim,

I dont have much experience in working with views, so comments as
this are one of my 'great reasons' for dedicating time to this site as I'm easily pointed to areas I need to study.

Perhaps this white paper can be of use on this topic, for determining performance enhancements in view design:

http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bi_dw_materialized_views_10gr2_0505.pdf

Here's the quote which caught my eye:

Quote:
Prior to creating a materialized view or once it is created, the DBA may wonder what is possible with this materialized view, such as it is fast refreshable and if not, why not. The procedure DBMS_MVIEW.EXPLAIN_MVIEW can provide this information


Inestigative techniques follow. Take a peruse before
concluding straight out it is the hierarchical aspect (which it may well be, of course).

Best Regards
Harry
Re: Another way to write hierarchical query...??? [message #349619 is a reply to message #349399] Mon, 22 September 2008 07:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can write a query that will mimic a hierarchical query, but only for a fied number of levels - if you want to display every records and it's grand-children, you need to join the table to itself twice.

As far as I know, there is no generic way of getting to an arbitrary depth in a hierarchy without using CONNECT BY
Previous Topic: trigger required on strange requirement
Next Topic: Sequene problem with Insert All
Goto Forum:
  


Current Time: Tue Dec 06 00:10:17 CST 2016

Total time taken to generate the page: 0.07195 seconds