March 15, 2011 at 5:53 am
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
(
Selectuser_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
(
Selectuser_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
March 15, 2011 at 6:33 am
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
October 31, 2011 at 8:23 pm
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 (
Selectuser_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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply