Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Explanation in CTE


Explanation in CTE

Author
Message
krishnakumar.palchamy
krishnakumar.palchamy
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 49
Dear All,

Can any one help me to solve the below problem?

Thanks in advance.

I am using Common Table Expression (CTE) in sql server 2008 to fetch the hierarchy of the users.

;with child_user(user_id, created_by)
as
(
Select user_id, created_by from User_MAster where user_id= 1195
union all
select ro.user_id, ro.created_by
from User_Master ro
join child_user cv on cv.user_id = ro.Created_by
)
select user_id, created_by from child_user CV

When I executing the above query, I am getting an error message as displayed "Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

After that I tried include the Option as below

;with child_user(user_id, created_by)
as
(
Select user_id, created_by from User_MAster where user_id= 1195
union all
select ro.user_id, ro.created_by
from User_Master ro
join child_user cv on cv.user_id = ro.Created_by
)
select user_id, created_by from child_user CV
option (maxrecursion 32765);

Even now I am getting same error message with "....maximum recursion 32765...."

I had only two records in my master table related to the above user_id.
BUt I am geting duplicate rows (32765 records).

Please help me to solve the same.

Thanks & Regards,
Krishna Kumar P
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
I suspect that what you've got is an infinite self-join in the way you've written the CTE. I'd need to see the definition of the tables and the data in them before I could be certain, but it looks like you're joining the first value to itself, which will create an infinite loop.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Toby Harman
Toby Harman
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 668
I suspect that one of your records in the User_Master table has the created_by set to the same value as the user_id.

This sample works fine for me, but uncommenting the 1st value in the insert and commenting out the second causes your error.


CREATE TABLE #User_Master (
user_id INT
, created_by INT)
;

INSERT #User_Master
VALUES
--(1195, 1195)
(1195, 1196)
, (1234, 1195)
, (5678, 1234)
;

with child_user(user_id, created_by)
as (
Select user_id, created_by
from #User_Master where user_id= 1195
union all
select ro.user_id, ro.created_by
from #User_Master ro
join child_user cv on cv.user_id = ro.Created_by
)
select
user_id, created_by from child_user CV

DROP TABLE #User_Master


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search