how to avoid circular references using CTE ?
-
I want to build a table-hiarchy-list using this query :
I know it still uses the sql2000 systemtables and will convert it to the propre ss2005 equivalent later on.
WITH ObjHiearchy (LevelID, Owner, TableName, ObjID)
AS
(
-- Anchor member definition
select 0 as LevelID, U.name as Owner , Parent.name as TableName , Parent.id as ObjID
from sysobjects Parent with (nolock)
inner join sysusers U with (nolock)
on Parent.uid = U.uid
left join sysreferences RefKey with (nolock)
on RefKey.rkeyid = Parent.id
and RefKey.rkeyid <> RefKey.fkeyid
where Parent.xtype= 'U'
and Parent.name <>'dtproperties'
and RefKey.fkeyid is null
UNION ALL
-- Recursive member definition
select H.LevelID - 1 , U.name as Owner , Parent.name as TableName , Parent.id
from sysobjects Child with (nolock)
inner join sysreferences RefKey with (nolock)
on RefKey.fkeyid = Child.id
and RefKey.rkeyid <> RefKey.fkeyid
inner join sysobjects Parent with (nolock)
on RefKey.rkeyid = Parent.id
inner join sysusers U with (nolock)
on Parent.uid = U.uid
and Parent.xtype= 'U'
and Parent.name <>'dtproperties'
inner join ObjHiearchy H
on H.ObjID = Child.id
)
select distinct *
from ObjHiearchy
order by levelid ,owner, tablename;
This works fine as long as there are no circular references in the databases object-schema.
Then it says
"Msg 530, Level 16, State 1, Procedure spc_TB_Hierarchie, Line 10
The statement terminated. The maximum recursion 32767 has been exhausted before statement completion."
This would mean I need except or not exists in my Recursive member definition part of the query, wright ?
How can I do this ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
This was removed by the editor as SPAM
-
Hi,
This might be of help for you:
http://www.theserverside.net/articles/showarticle.tss?id=HeirarchicalQueries
-
Thanks for the link, wich is a nice article.
The problem with these circular references is that I would need to exclude all "new" items that would already be in the result.
This would mean a dynamic " and boss = 12 ", as referenced in the article.
I guess these kind of problems cannot be solved with a CTE
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply