Nevermind... all I could see were nails. I'll create a temp table for the regular navigation and use that table for breadcrumbs (the depth is accurately created on that query).
Jason Durham On Tue, Mar 29, 2011 at 1:44 PM, Jason Durham <jqdur...@gmail.com> wrote: > I apologize if this is an inappropriate place to ask this question. > > I'm using a CTE for a recursive query to assemble navigation. In the CTE, > I'm creating a column that contains the depth of the navigation. This depth > is ultimately inserted into the class of an HTML element. I'm refactoring > how my breadcrumbs are created and want to use a similar method. The catch > is, breadcrumbs start from the bottom of a recursive tree and work their way > up. This is causing undesired behavior on the depth calculation. The > bottom-most element is 0. It's parent is 1. It's grandparent is 2 (and so > forth). I need to reverse the order of this field. > > I'm currently trying to use a cursor to loop back over the CTE and update > the depth field manually. However, it appears that I'm I can't access the > data stored in the temporary table (CTE) from with in the cursor. Should I > be going about this a different way? > > Here is the SQL... > > DECLARE @THEID INT > SET @THEID=88; > > DECLARE BC_Cursor CURSOR FOR > WITH Breadcrumbs(Navigation_ID, Parent_ID, Navigation_Name, Depth) AS > ( > SELECT Navigation_ID, Parent_ID, Navigation_Name, 0 > FROM Navigation > WHERE Navigation_ID = @THEID > > UNION ALL > > SELECT n.Navigation_ID, n.Parent_ID, n.Navigation_Name, bc.Depth+1 > FROM Navigation n > INNER JOIN Breadcrumbs bc > ON bc.Parent_ID = n.Navigation_ID > ) > SELECT * FROM Breadcrumbs OPTION (MAXRECURSION 8) > FOR UPDATE; > > DECLARE @maxRows int; > SET @maxRows = @@ROWCOUNT; <--- Will be used to renumber the depth > > OPEN BC_Cursor; > FETCH NEXT FROM BC_Cursor; > WHILE @@FETCH_STATUS = 0 > BEGIN > FETCH NEXT FROM BC_Cursor; <--- This prints each row. Instead of > outputting them, I need to update and ultimately return the whole (virtual) > table. > END; > CLOSE BC_Cursor; > > DEALLOCATE BC_Cursor; > > > Thanks in advance. > > Jason Durham > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343385 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
