Re: SOT: SQL CTE update
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> 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 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 >> 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
Re: SOT: SQL CTE update
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 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 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 > 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
Re: SOT: SQL CTE update
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 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 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:343386 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SOT: SQL CTE update
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 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
SOT: SQL CTE update
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:343383 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
