Hierarchial data problem

  • 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.??

  • No replies ????

  • 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.

  • 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/

  • 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

  • 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