May 29, 2012 at 4:08 am
CREATE TABLE Organization
(
EmployeeID hierarchyid NOT NULL,
EmployeeName nvarchar(50)NOT NULL
)
WITH UpperHierarchy(EmpId, LastName, Node)
AS
(
SELECT EmployeeId, LoginId,hierarchyid::GetRoot()
FROM HumanResources.Employee
WHERE ManagerId is Null
UNION ALL
SELECT Sub.EmployeeId, Sub.LoginId, Parent.Node.GetDescendant(null, null)
FROM HumanResources.Employee AS Sub
INNER JOIN UpperHierarchy AS Parent
ON Sub.ManagerId = Parent.EmpId
)
Insert Into dbo.Organization(EmployeeId, EmployeeName)
Select Node, LastName From UpperHierarchy
because NULL has been used in the getdescendant function so all the child nodes from the same parent are having similar hierarchyId's. Is there a way that we have unique Id' for every node.??
May 30, 2012 at 9:52 pm
No replies ????
May 30, 2012 at 11:48 pm
So it looks like you are trying to work through the technet example Converting a Table to a Hierarchical Structure (http://technet.microsoft.com/en-us/library/bb677237.aspx and have gotten stuck on the section where you populate the table (http://technet.microsoft.com/en-us/library/bb630263.aspx).
Just as a reference, I think people would have been more likely to respond if you had started by giving that information in advance. Otherwise they are forced to try to guess what you are referencing and what you're trying to do.
Here is the original code:
WITH paths(path, EmployeeID)
AS (
-- This section provides the value for the root of the hierarchy
SELECT hierarchyid::GetRoot() AS OrgNode, EmployeeID
FROM #Children AS C
WHERE ManagerID IS NULL
UNION ALL
-- This section provides values for all nodes except the root
SELECT
CAST(p.path.ToString() + CAST(C.Num AS varchar(30)) + '/' AS hierarchyid),
C.EmployeeID
FROM #Children AS C
JOIN paths AS p
ON C.ManagerID = P.EmployeeID
)
INSERT NewOrg (OrgNode, O.EmployeeID, O.LoginID, O.ManagerID)
SELECT P.path, O.EmployeeID, O.LoginID, O.ManagerID
FROM EmployeeDemo AS O
JOIN Paths AS P
ON O.EmployeeID = P.EmployeeID
GO
You seem to have bypassed the first step of building the temp table and EmployeeDemo and are trying to use the source table (HumanResources.Employee) as a substitute.
In your example, instead of dynamically building the hierarchy node path with the recursive CTE, you are trying to use the GetDescendant function to capture the the node path that way. The problem is that the you don't have any descendants yet because you are in the process of adding them. The problem is that GetDescendants is returning the same child node each time because it's referring to the same parent (see 2nd post for more details).
If you follow the original example, you should be able to build the table correctly.
May 31, 2012 at 1:12 am
Not technet, actually i was reading an article on sqlservercentral itself and that's where i found that code. Should have specified it earlier.
http://www.sqlservercentral.com/articles/SQL+Server+2008/62204/
May 31, 2012 at 1:58 am
Okay, this makes sense now (and I feel a great deal less clever).
So GetDescendant returns a child node of the parent.
According to BOL:
parent.GetDescendant ( child1 , child2 )
Returns one child node that is a descendant of the parent.
If parent is NULL, returns NULL.
If parent is not NULL, and both child1 and child2 are NULL, returns a child of parent.
So the original query starts out with all employees with a manager ID of NULL. In AdventureWorks, there is one: adventure-works\ken0
He has six employees:
adventure-works\david0
adventure-works\terri0
adventure-works\jean0
adventure-works\laura1
adventure-works\james1
adventure-works\brian3
Each one of those has a hierarchyID value of 0x58. The string representation of this value is '/1/'
Since ken0's hiearchyID is '/' then the GetDescedants value of '/1/' for ken0 is correct. It is the next level down from '/'.
You can see the string representation by adjusting the code:
Select Node, LastName, Node.ToString() AS node_path
From UpperHierarchy
But I think what you want to see is a unique node path for each employee. I don't know why the original article did not include that. But you can achieve by implementing something similar to the Technet article I originally thought you were using:
WITH EmpList AS (
SELECT
EmployeeID,
LoginID,
ManagerID,
ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID) AS Num
FROM HumanResources.Employee
)
,UpperHierarchy (EmpId, LastName, Node)
AS
(
SELECT EmployeeId, LoginId,hierarchyid::GetRoot()
FROM EmpList
WHERE ManagerId is Null
UNION ALL
SELECT Sub.EmployeeId, Sub.LoginId, CAST(Parent.Node.ToString() + CAST(Sub.Num AS VARCHAR(30)) + '/' AS hierarchyid)
FROM EmpList AS Sub
INNER JOIN UpperHierarchy AS Parent
ON Sub.ManagerId = Parent.EmpId
)
--Insert Into dbo.Organization(EmployeeId, EmployeeName)
Select Node, LastName, Node.ToString() AS Node_Path
From UpperHierarchy
May 31, 2012 at 2:39 am
Thnx for the help.. The output is similar to what i was looking for..
🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply