Jason, That's why I said to sort the tree in using "DESC" instead of "ASC" on the Depth column:
> - - -*Drivers *(3) > - -Software (2) > -Support (1) > Home (0) If you pull in descending order, you'll know the max depth from the first row, so if it's a matter of using the depth for indenting, just use: maxDepth-depth to reverse the depth. -Dan On 3/29/2011 3:34 PM, Jason Durham wrote: > > 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:343389 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
