The in example I posted, I'm calculating the depth in the select statement. The depth is determined by the order in which the row is returned. If I start from a child, the child is returned first (therefore it's 0). If I start from the parent (top level nav), the parent is 0. Consider this...
Typical nav.. (depth is in parenthesis). The tree is built from the top down. Home (0) -Products (1) - -Car (2) - -Truck (2) -Support (1) - -Software (2) - - -*Drivers *(3) Breadcrumbs... The tree is built from the bottom up. *Drivers *(0) -Software (1) - -Support (2) - - -Home (3) The "depth" is backwards. The depth is not a fixed column. Like I said... the solution hit me. The entire site's navigation is generated with the correct depth onAppStart(). I'll just cache it via a temp table. Jason Durham On Tue, Mar 29, 2011 at 2:14 PM, Dan G. Switzer, II <[email protected] > wrote: > > Why bother with cursors or temp tables at all and just sort across Depth > desc instead of Depth asc? > > -Dan > > On Tue, Mar 29, 2011 at 2:59 PM, Jason Durham <[email protected]> wrote: > > > > > 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 <[email protected]> > 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:343388 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
