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
