Prev: SQL injection attacks
Next: Script to generate SQL Agent jobs.
From: trullock on 17 Jul 2008 08:00
Hi, can someone help me out with a query, im really stuck as to why
this doesnt work :(

Ive got a tree of data, with the tree stored using nested sets. Each
node on the tree has a "mode". mode 0 means "inherit the mode from my
parent", anything other than 0 is an explicit mode. I need to
calculate the inherited mode of each node, so that in the case that a
node has the mode "0", i can look its inherited mode and use that.

Here is how i want the data returned, "left" and "mode" are properties
of each tree node, and i need to calculated "inherited mode".

Left | Mode | Inherited Mode
10 | 0 | 1
9 | 1 | 2
8 | 0 | 2
7 | 2 | 0
6 | 0 | 0

What im doing to return the above table is:

SELECT T1.Left, T1.Mode, dbo.fn_Mode_Inherited(T1.NodeId) AS
Mode_Inherited
FROM tblTree T1
INNER JOIN tblTree T2 ON T1.Left < pT.Left
AND T1.Right > pT.Right
ORDER BY T1.Left DESC

(there are where clauses to narrow me down to a specific part of the
tree, but theyre irrelevant)

inside the function is the following code:

DECLARE @Mode_Inherited INT
SET @Mode_Inherited = 0
SELECT @Mode_Inherited = CASE
WHEN @Mode_Inherited = 0 THEN T1.Mode
ELSE @Mode_Inherited END
FROM tblTree T1
INNER JOIN tblTree T2 ON T1.Left < T2.Left
AND T1.Right > T2.Right
AND T2.NodeId = NodeId
ORDER BY T1.Left DESC
RETURN @Mode_Inherited


The problem is that my "inherited mode" column is always returned as
0, and i have no idea why.

Any help would be greatly appreciated

Thanks!
Andrew
From: jhofmeyr on 17 Jul 2008 09:21
Hi Andrew,

Why are you using a function for this?
Wouldn't:
SELECT T1.Left, T1.Mode, CASE T1.Mode WHEN 0 THEN T2.Mode
ELSE T1.Mode END AS Inherited_Mode
FROM tblTree T1
INNER JOIN tblTree T2 ON T1.Left < pT.Left
AND T1.Right >
pT.Right
ORDER BY T1.Left DESC

do the same thing?

J
 | 
Pages: 1
Prev: SQL injection attacks
Next: Script to generate SQL Agent jobs.