Re: A CTE Question

From: The Boss <usenet_at_No.Spam.Please.invalid>
Date: Thu, 26 Aug 2010 23:10:49 +0200
Message-ID: <4c76d859$0$23627$e4fe514c_at_dreader25.news.xs4all.nl>

Thomas Kellerer wrote:
> Hi,
>
> 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 from t_recurs r
> order by 1;
>
> Now, whether that will be fast enough is a completely different topic
> ;)
> Thomas

I can't answer on behalf of Michel, just wanted to say he has asked the same question in comp.databases.ibm-db2 (and did receive a couple of good answers).

Cheers!

-- 
Jeroen 

Received on Thu Aug 26 2010 - 16:10:49 CDT