Re: A CTE Question
Date: Sat, 28 Aug 2010 12:56:33 +0200
Message-ID: <8ds8asFhjjU1_at_mid.individual.net>
Erland Sommarskog wrote on 28.08.2010 11:36:
>>> 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 recursion as works: you start from the bottom and work your
> way up. You will stop recurse when you reach the top level. However,
> he would need a WHERE NOT EXISTS to only start at the leaves. Now
> he starts on all nodes.
Hmm, for me the above query yields an endless "loop" when you remove the " iter < 1000"
>> 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;
>
> I don't think this works.
It works on PostgreSQL with the test data supplied :)
> (And uses -2 rather than NULL to signify a root.)
Yes, correct. Copy & Paste error. I just don't like "magic values" ;)
> As for performance, on SQL Server it is certainly recommendable to
> to first save the result of the CTE into a temp table and work from
> there.
Temp tables are (most of the time) a real performance killer in the Oracle world.
Thomas Received on Sat Aug 28 2010 - 05:56:33 CDT