Re: A CTE Question

I also would like to know if you are really using Oracle.

As I don't have Oracle 11gR2 available (which is the first Oracle version to support recursive CTEs) I tested this using PostgreSQL.

I expect this to run on Oracle 11gR2 as well.

> with t_recurs (dep_id, parent, iter) as
> (
> select dep_id, parent, 0 from DEPARTMENT
> union all
> select r.dep_id, d.parent, iter + 1
> from t_recurs r, DEPARTMENT d
> where r.parent = d.dep_id and iter< 1000
> )

That query is wrong, and that's why you need this "where iter < 1000" workaround.

The basic recursion should be like this:

with recursive t_recurs (dep_id, parent, iter) as (

   select dep_id, parent, 0 as iter
   from department
   where parent = -2    

   union all    

   select d.dep_id, d.parent, iter + 1
   from department d
     join t_recurs r on d.parent = r.dep_id )
select *
from t_recurs;

I changed two things:
- the where condition for the first part of the union - selecting the d.dep_id instead of r.dep_id in the recursion part which makes the check for an infinite loop obsolete   

Now to your real question:

> What am I actually looking for is also the count of Persons that are
> in all sub-departments in that hierarchy, including the departement a
> person is currently in.

this should do it as far as I can tell.

with recursive t_recurs (dep_id, parent, iter) as (

   select dep_id, parent, 0 as iter
   from department
   where parent is null    

   union all    

   select d.dep_id, d.parent, iter + 1
   from department d
     join t_recurs r on d.parent = r.dep_id )
select r.dep_id,

        (select count(*) from t_recurs r2 where r2.iter > r.iter) as department_count,
        (select count(*) from person where dep_id = any (select dep_id from t_recurs r3 where r3.iter >= r.iter)) as person_count

Now, whether that will be fast enough is a completely different topic ;)

Thomas Received on Thu Aug 26 2010 - 15:49:20 CDT