Michel Cadot wrote:
> "Maxim Demenko" <mdemenko_at_arcor.de> a écrit dans le message de news: 4524c3f1$0$13086$9b4e6d93_at_newsspool2.arcor-online.net...
> | Michel Cadot schrieb:
> | > "Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1159993002.453179.263340_at_e3g2000cwe.googlegroups.com...
> | >
> | > Mike C wrote:
> | >> Michel Cadot wrote:
> | >>> "Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1159989707.458848.89750_at_c28g2000cwb.googlegroups.com...
> | >>> I've already used the connect by to get the data in this format....
> | >>>
> | >>> -----------------------
> | >>>
> | >>> - Don't top post
> | >>> - So go on, you can easely get the sum over the hierarchy with a connect by.
> | >>>
> | >>> Regards
> | >>> Michel Cadot
> | >> This doesn't really need a CONNECT BY as it only needs to look at 1
> | >> level below. To get the sum for level 1, it only needs to look at
> | >> level 2, etc. If I could "easily get the sum" I would have done it and
> | >> wouldn't have posted to the group!
> | >
> | > I know I can write it something like this, but I was hoping to not have
> | > to repeat code and do it using one of the analytic fucntions if
> | > possible.
> | >
> | > SELECT NAME, level, decode(level,4,t1.frequency,t2.frequency) from
> | > table1 t1,
> | > (select parent_id, sum(frequency) FROM table1 group by parent_id) t2
> | > WHERE t1.id=t2.parent_id(+)
> | >
> | > --------------------------
> | >
> | > Analytic functions are useless here.
> | > From your exemple, you have to walk through the whole hierarchy
> | > and not just one level (else how do you 30 for id 1?).
> | > And connect by is the only way to go through a hierarchy.
> | > I don't think you can get something with your query.
> | >
> | > Regards
> | > Michel Cadot
> | >
> | >
> | The straightforward approach would be probably like
> |
> | SELECT Empno,
> | Ename,
> | Sal + Nvl((SELECT SUM(Sal)
> | FROM Scott.Emp i
> | CONNECT BY PRIOR Empno = Mgr
> | START WITH o.Empno = i.Mgr),
> | 0) Total_SubTree_Sal
> | FROM Scott.Emp o
> |
> | Not sure, about the performance with correlated hierarchical subquery,
> | whereas with not too big volumes of data ( and not too deep hierarchy)
> | should be ok.
> |
> | It might be interesting, if this result can be achieved without
> | correlated subquery however...
> |
> | Best regards
> |
> | Maxim
>
> I don't think so.
select
a.id,
a.name,
parent,
sum(b.frequency)
from
(select id, name, parent, sys_connect_by_path(id, '/') path
from t1 connect by prior id = parent start with parent is null
) a,
(select frequency, sys_connect_by_path(id, '/') path
from t1 connect by prior id = parent start with parent is null
) b
where instr(b.path, a.path) = 1
group by a.id, a.name, a.parent
/
ID NAME PARENT SUM(B.FREQUENCY)
1 USA 30
2 CA 1 25
3 LA 2 10
4 SF 2 15
5 IL 1 5
6 Chicago 5 5
> A comment on your query: why starting with o.empno=i.mgr
> and not o.empno=i.empno? This prevent from adding current sal
> and testing if sum(sal) of children is null or not:
>
> SQL> select * from t order by id;
> ID NAME PARENT FREQUENCY
> ---------- ---------- ---------- ----------
> 1 USA
> 2 CA 1
> 3 LA 2 10
> 4 SF 2 15
> 5 IL 1
> 6 Chicago 5 5
>
> 6 rows selected.
>
> SQL> select id, name, parent,
> 2 ( select sum(frequency)
> 3 from t t2
> 4 connect by prior id = parent
> 5 start with t2.id = t1.id ) frequency
> 6 from t t1
> 7 /
> ID NAME PARENT FREQUENCY
> ---------- ---------- ---------- ----------
> 1 USA 30
> 2 CA 1 25
> 3 LA 2 10
> 4 SF 2 15
> 5 IL 1 5
> 6 Chicago 5 5
>
> 6 rows selected.
>
> Regards
> Michel Cadot
Received on Thu Oct 05 2006 - 10:57:53 CDT