tag:blogger.com,1999:blog-29286778.post115158279183739445..comments2023-07-02T05:53:35.132-04:00Comments on SQL Garbage Collector: Interesting queries using recursive Common Table ExpressionsUnknownnoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-29286778.post-25950540417377864902010-06-21T13:56:02.886-04:002010-06-21T13:56:02.886-04:00wow.....great...!
It's very helpful!
good job....wow.....great...!<br />It's very helpful!<br />good job.<br /><br />Regards,<br />Biju.K.SAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-75288660016291787632007-09-11T05:28:00.000-04:002007-09-11T05:28:00.000-04:00hi!i have a table named "Unit"which has to main co...hi!<BR/>i have a table named "Unit"<BR/>which has to main columns<BR/>unitid and parentunitid.<BR/>for a given search depth and<BR/>rootunit ,i need to retreive all units under this root<BR/>i tried many variations like:<BR/> WITH UnitsCTE(UnitId,level) AS<BR/> (<BR/> -- Anchor member definition<BR/> SELECT u.UnitId ,1 as level<BR/> FROM qf.Unit u<BR/> WHERE u.UnitId = @RootUnitId <BR/> UNION ALL<BR/> -- Recursive member definition<BR/> SELECT u.UnitId,level + 1 <BR/> FROM qf.Unit u<BR/> JOIN UnitsCTE cte ON u.UnitId = cte.UnitId<BR/> WHERE u.ParentUnitId = @RootUnitId and level < @depth<BR/> )<BR/> SELECT *<BR/> FROM UnitsCTE<BR/><BR/> and got nothing<BR/>do anyone has an idea?<BR/>thanks in advanceAnonymousnoreply@blogger.com