Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Explanation in CTE Expand / Collapse
Author
Message
Posted Tuesday, March 15, 2011 5:53 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, June 20, 2011 5:55 AM
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
Post #1078252
Posted Tuesday, March 15, 2011 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1078278
Posted Monday, October 31, 2011 8:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 3:50 PM
Points: 405, Visits: 563
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

Post #1198416
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse