Re: A CTE Question
Date: Sat, 28 Aug 2010 15:14:48 +0200
Message-ID: <8dsge0F12rU1_at_mid.individual.net>
Erland Sommarskog wrote on 28.08.2010 14:38:
> I noticed that in your own query you used the RECURSIVE keyword which
> is unknown to SQL Server. Maybe that is needed on PostgreSQL to
> prevent recursion from continuing?
No. It's required by the ANSI standard...
>> It works on PostgreSQL with the test data supplied :)
>
> Interesting. I certainly don't get the expected result on SQL Server.
> I don't have PostgresSQL, but it looks like a bug to me. If you try the
> extra test data I composed, does it still work? (See below.)
Note that the OP incorrectly (at least in my eyes) selects the the dep_id from the t_recurs not from the department table, and I think that is causing the problem with the endless loop:
with t_recurs (dep_id, parent, iter) as
(
select dep_id, parent, 0 from DEPARTMENT
union all
select r.dep_id, <-- !!! this should be D.dep_id !!!!
d.parent, iter + 1
from t_recurs r, DEPARTMENT d
where r.parent = d.dep_id and iter < 1000
)
It should be d.dep_id (because r.dep_id will create a cycle). As soon as I select the dep_id from the department table in the second part of the union, the endless loop stops.
>> It works on PostgreSQL with the test data supplied :)
> Interesting. I certainly don't get the expected result on SQL Server.
> I don't have PostgresSQL, but it looks like a bug to me. If you try the
> extra test data I composed, does it still work? (See below.)
You are right, it's counting too many rows ;)
Because I'm simply counting everything below the current hierarchy level, not along the current branch. Your solution with taking the full path deals correctly with that.
Regards
Thomas
Received on Sat Aug 28 2010 - 08:14:48 CDT