mssql2012 - hierarchy traversal does not bring back the expected result set.

When I try to look for descendent organizations, I am seeing the level I am descending from and the descendents (screen shot).  I did not expect to see the row hightlighted.
Problem-.JPG
When I try to find the ancestor on level 1 for OrgId 3, I don't receive any rows. I expected to see only the highlighted row. I have either generated the data incorrectly, or I am thinking about hierarchies in a wrong way.

Here is the sql for the queries

-- query#1 (descendents)

  declare @OrgIdofInterest hierarchyid

  select @OrgIdofInterest = OrgNode
   from [stakeholder].OrganizationInheritance
   where OrgId =2;


 SELECT 
 	 orgtree.OrgNode.ToString() AS Text_OrgNode, 
     orgtree.OrgNode, 
	 orgtree.OrgLevel,
	 orgtree.OrgId 
     FROM stakeholder.OrganizationInheritance orgtree
    where OrgNode.IsDescendantOf(@OrgIdofInterest) = 1


-- query #2 (ancestor[s])

  declare @NewOrgIdofInterest hierarchyid

  select @NewOrgIdofInterest = OrgNode
   from [stakeholder].OrganizationInheritance
   where OrgId = 3;


 SELECT 
 	 orgtree.OrgNode.ToString() AS Text_OrgNode, 
     orgtree.OrgNode, 
	 orgtree.OrgLevel,
	 orgtree.OrgId 
     FROM stakeholder.OrganizationInheritance orgtree
     where OrgNode.GetAncestor(1) = @NewOrgIdofInterest

Open in new window


Here is the build SQL for the tables and stored proc.

use [scratchdb]
go


ALTER TABLE stakeholder.OrganizationInheritance
	DROP CONSTRAINT FK_OrganizationInheritance_OrganizationType
GO

ALTER TABLE stakeholder.OrganizationInheritance
	DROP CONSTRAINT FK_OrganizationInheritance_Organization
GO

ALTER TABLE stakeholder.Organization
	DROP CONSTRAINT FK_Organization_OrganizationType
GO

ALTER TABLE stakeholder.OrganizationInheritance
	DROP CONSTRAINT UQ__Organiza__420C9E6DA211482A
GO

ALTER TABLE stakeholder.OrganizationInheritance
	DROP CONSTRAINT PK_OrganizationInheritance
GO

ALTER TABLE stakeholder.Organization
	DROP CONSTRAINT PK_Organization
GO

DROP INDEX stakeholder.OrganizationInheritance.OrganizationNode
GO

DROP TABLE stakeholder.OrganizationInheritance
GO

DROP TABLE stakeholder.Organization
GO


CREATE TABLE stakeholder.Organization ( 
	OrgId           	int IDENTITY NOT NULL,
	LegacyOrgCode   	nvarchar(50) NULL,
	CustomIdentifier	nvarchar(50) NULL,
	OrgTypeId       	int NULL,
	OrgShortName    	nvarchar(50) NULL,
	OrgName         	nvarchar(200) NULL,
	OrgURL          	nvarchar(300) NULL,
	associationYears	smallint NULL 
	)
GO

CREATE TABLE stakeholder.OrganizationInheritance ( 
	OrgNode  	hierarchyid NOT NULL,
	OrgLevel 	smallint NULL,
	OrgId    	int NOT NULL,
	OrgName  	varchar(200) NOT NULL,
	OrgTypeId	int NOT NULL 
	)
GO


SET IDENTITY_INSERT stakeholder.Organization ON
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(-1, N'-1', N'', 1, N'All Fleets', N'ALL FLEETS', NULL, NULL)
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(1, N'506', NULL, 1, N'S', N'S-0', NULL, NULL)
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(2, N'626', NULL, 1, N'JJ Inc.', N'JJ-0', NULL, NULL)
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(3, N'69066', NULL, 1, N'JJ-Northeast', N'JJ-1-A', NULL, NULL)
GO
INSERT INTO stakeholder.Organization(OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears)
  VALUES(4, N'70809', NULL, 1, N'JJ-Southeast', N'JJ-1-B', NULL, NULL)
GO
SET IDENTITY_INSERT stakeholder.Organization OFF
GO

CREATE PROCEDURE [stakeholder].[AddOrganizationInheritance]
(  
	 @topOrgId int,
	 @Orgid int, 
	 @OrgName varchar(100), 
	 @OrgTypeId int
	
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   DECLARE @mOrgNode hierarchyid, @lc hierarchyid

   SELECT @mOrgNode = OrgNode 
    FROM  UnifiedStakeholder.stakeholder.OrganizationInheritance
   WHERE OrgId = @topOrgId

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

   BEGIN TRANSACTION
      SELECT @lc = max(OrgNode) 
      FROM UnifiedStakeholder.stakeholder.OrganizationInheritance
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

      INSERT UnifiedStakeholder.stakeholder.OrganizationInheritance 
	  (OrgNode, OrgId, OrgName, OrgTypeId)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @OrgId, @OrgName, @OrgTypeId)

   COMMIT
	
END

GO

 	 insert UnifiedStakeholder.stakeholder.OrganizationInheritance
	 (OrgNode, OrgId,OrgName,OrgTypeId)
	 values (hierarchyid::GetRoot(),-1,'ALL FLEETS',1)
GO
	 
	 
	 insert UnifiedStakeholder.stakeholder.OrganizationInheritance
	 (OrgNode, OrgId,OrgName,OrgTypeId)
	 values (@Fleet.GetDescendant(NULL,NULL),1,'S',1)
GO

-- // Note: I could have this wrong. I think I'm putting in the level above the level I want as @topOrgId.
exec unifiedstakeholder.stakeholder.AddOrganizationInheritance -1,2,'JJ Inc.',1
GO

exec unifiedstakeholder.stakeholder.AddOrganizationInheritance 2,3,'JJ-Northeast',1
GO

exec unifiedstakeholder.stakeholder.AddOrganizationInheritance 2,4,'JJ-Southeast',1
GO

CREATE UNIQUE INDEX OrganizationNode
	ON stakeholder.OrganizationInheritance(OrgNode, OrgLevel)
	WITH FILLFACTOR = 95
GO

ALTER TABLE stakeholder.Organization
	ADD CONSTRAINT PK_Organization
	PRIMARY KEY (OrgId)
GO

ALTER TABLE stakeholder.OrganizationInheritance
	ADD CONSTRAINT PK_OrganizationInheritance
	PRIMARY KEY (OrgNode)
GO

ALTER TABLE stakeholder.OrganizationInheritance
	ADD CONSTRAINT UQ__Organiza__420C9E6DA211482A
	UNIQUE (OrgId)
GO

ALTER TABLE stakeholder.Organization
	ADD CONSTRAINT FK_Organization_OrganizationType
	FOREIGN KEY(OrgTypeId)
	REFERENCES reference.OrganizationType(OrgTypeId)
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO

ALTER TABLE stakeholder.OrganizationInheritance
	ADD CONSTRAINT FK_OrganizationInheritance_OrganizationType
	FOREIGN KEY(OrgTypeId)
	REFERENCES reference.OrganizationType(OrgTypeId)
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO

ALTER TABLE stakeholder.OrganizationInheritance
	ADD CONSTRAINT FK_OrganizationInheritance_Organization
	FOREIGN KEY(OrgId)
	REFERENCES stakeholder.Organization(OrgId)
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION 
GO

Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Query 1: See IsDescendantOf()

Remarks

Returns true for all the nodes in the sub-tree rooted at parent, and false for all other nodes.

Parent is considered its own descendant.

E.g.
USE tempdb;
GO

DECLARE @OrganizationInheritance TABLE
    (
      OrgNode HIERARCHYID ,
      OrgId INT ,
      OrgName VARCHAR(200)
    );

INSERT  INTO @OrganizationInheritance
VALUES  ( '/1/', -1, N'All Fleets' ),
        ( '/1/1/', 1, N'S' ),
        ( '/1/1/1/', 2, N'JJ Inc.' ),
        ( '/1/1/1/1/', 3, N'JJ-Northeast' ),
        ( '/1/1/1/2/', 4, N'JJ-Southeast' );

SELECT  *
FROM    @OrganizationInheritance;

-- query#1 (descendents)
DECLARE @OrgIdofInterest HIERARCHYID;

SELECT  @OrgIdofInterest = OrgNode
FROM    @OrganizationInheritance
WHERE   OrgId = 2;

SELECT  OrgNode.ToString() AS Text_OrgNode ,
        OrgNode.GetLevel() AS Level ,
        *
FROM    @OrganizationInheritance
WHERE   OrgNode.IsDescendantOf(@OrgIdofInterest) = 1
        AND OrgNode != @OrgIdofInterest;


-- query #2 (ancestor[s])
SELECT  @OrgIdofInterest = OrgNode
FROM    @OrganizationInheritance
WHERE   OrgId = 2;

SELECT  OrgNode.ToString() AS Text_OrgNode ,
        OrgNode.GetLevel() AS Level ,
        *
FROM    @OrganizationInheritance
WHERE   OrgNode = @OrgIdofInterest.GetAncestor(1);

Open in new window

0
 
ste5anSenior DeveloperCommented:
Your sample is good, but @Fleet is not defined, nor used. This is your sample, after I made it run:

USE tempdb;
GO

CREATE TABLE Organization
    (
      OrgId INT IDENTITY
                NOT NULL ,
      LegacyOrgCode NVARCHAR(50) NULL ,
      CustomIdentifier NVARCHAR(50) NULL ,
      OrgTypeId INT NULL ,
      OrgShortName NVARCHAR(50) NULL ,
      OrgName NVARCHAR(200) NULL ,
      OrgURL NVARCHAR(300) NULL ,
      associationYears SMALLINT NULL
    );

CREATE TABLE OrganizationInheritance
    (
      OrgNode HIERARCHYID NOT NULL ,
      OrgLevel SMALLINT NULL ,
      OrgId INT NOT NULL ,
      OrgName VARCHAR(200) NOT NULL ,
      OrgTypeId INT NOT NULL
    );
GO

CREATE PROCEDURE AddOrganizationInheritance
    (
      @topOrgId INT ,
      @Orgid INT ,
      @OrgName VARCHAR(100) ,
      @OrgTypeId INT
	
    )
AS
    SET NOCOUNT ON;

    DECLARE @mOrgNode HIERARCHYID ,
        @lc HIERARCHYID;

    SELECT  @mOrgNode = OrgNode
    FROM    OrganizationInheritance
    WHERE   OrgId = @topOrgId;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;

    SELECT  @lc = MAX(OrgNode)
    FROM    OrganizationInheritance
    WHERE   OrgNode.GetAncestor(1) = @mOrgNode;

    INSERT  OrganizationInheritance
            ( OrgNode ,
              OrgId ,
              OrgName ,
              OrgTypeId
            )
    VALUES  ( @mOrgNode.GetDescendant(@lc, NULL) ,
              @Orgid ,
              @OrgName ,
              @OrgTypeId
            );

    COMMIT TRANSACTION;	
GO

SET IDENTITY_INSERT Organization ON;

INSERT  INTO Organization
        ( OrgId, LegacyOrgCode, CustomIdentifier, OrgTypeId, OrgShortName, OrgName, OrgURL, associationYears )
VALUES  ( -1, N'-1', N'', 1, N'All Fleets', N'ALL FLEETS', NULL, NULL ),
        ( 1, N'506', NULL, 1, N'S', N'S-0', NULL, NULL ),
        ( 2, N'626', NULL, 1, N'JJ Inc.', N'JJ-0', NULL, NULL ),
        ( 3, N'69066', NULL, 1, N'JJ-Northeast', N'JJ-1-A', NULL, NULL ),
        ( 4, N'70809', NULL, 1, N'JJ-Southeast', N'JJ-1-B', NULL, NULL );

SET IDENTITY_INSERT Organization OFF;
GO

INSERT  OrganizationInheritance
        ( OrgNode, OrgId, OrgName, OrgTypeId )
VALUES  ( hierarchyid::GetRoot(), -1, 'ALL FLEETS', 1 );
        --( @Fleet.GetDescendant(NULL, NULL), 1, 'S', 1 );
GO

SELECT  *
FROM    Organization O;

SELECT  *
FROM    OrganizationInheritance OI;
GO

-- // Note: I could have this wrong. I think I'm putting in the level above the level I want as @topOrgId.
--EXECUTE AddOrganizationInheritance -1, 2, 'JJ Inc.', 1;
--EXECUTE AddOrganizationInheritance 2, 3, 'JJ-Northeast', 1;
--EXECUTE AddOrganizationInheritance 2, 4, 'JJ-Southeast', 1;
--GO

-- query#1 (descendents)
DECLARE @OrgIdofInterest HIERARCHYID;

SELECT  @OrgIdofInterest = OrgNode
FROM    OrganizationInheritance
WHERE   OrgId = 2;

SELECT  orgtree.OrgNode.ToString() AS Text_OrgNode ,
        orgtree.OrgNode ,
        orgtree.OrgLevel ,
        orgtree.OrgId
FROM    OrganizationInheritance orgtree
WHERE   OrgNode.IsDescendantOf(@OrgIdofInterest) = 1;


-- query #2 (ancestor[s])
DECLARE @NewOrgIdofInterest HIERARCHYID;

SELECT  @NewOrgIdofInterest = OrgNode
FROM    OrganizationInheritance
WHERE   OrgId = 3;


SELECT  orgtree.OrgNode.ToString() AS Text_OrgNode ,
        orgtree.OrgNode ,
        orgtree.OrgLevel ,
        orgtree.OrgId
FROM    OrganizationInheritance orgtree
WHERE   OrgNode.GetAncestor(1) = @NewOrgIdofInterest;
GO
     
DROP PROCEDURE AddOrganizationInheritance;
DROP TABLE Organization;
DROP TABLE OrganizationInheritance;
GO

Open in new window


Can you please explain what you're trying to do here?
0
 
Paula DiTalloIntegration developerAuthor Commented:
Ste5an,

Sure. In a nut shell, I want to know why the levels don't work as I think they should.  I think that in the 1st example, I should only get back 2 rows--the children of JJ Inc.  Instead, I am getting back all 3 rows. In the second example, while solving for the Northeast's division, I am expecting to see only a single row for JJ Inc.  Instead, I'm getting back nothing.
0
 
Paula DiTalloIntegration developerAuthor Commented:
Anyone able to answer this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.