SQL Server CTE statement Varbinary(MAX) Not Returning the RootID

Question

I have a hierarchy table to store a binary tree. I'm using a recursive query to retrieve the tree level based on the IntroducerID as the "RootID". The value returned as what I expected, but after the AgentId number increment reached 116, this CTE query doesn't return the Level value in hierarchical form.

Like it cannot trace the RootID anymore.

This is what my table structure looks like;

CREATE TABLE [dbo].[TblHierarchy]
(
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [AgentID] [bigint] NULL,
      [AgentName] [varchar](50) NULL,
      [RootID] [bigint] NULL,
      [IntroducerID] [bigint] NULL,
      [Description] [varchar](50) NULL,
      [HierarchyTree] [hierarchyid] NULL
) ON [PRIMARY]

Below is the sample data from the table;

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (1, 1, N'Toh', 0, NULL, N'', NULL)

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (2, 2, N'Man', 1, 0, N'Child of Toh', NULL)

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (3, 3, N'Rul', 1, 0, N'Child of Toh', NULL)

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (4, 4, N'Rafiq', 2, 2, N'Child of Man', NULL)

INSERT [dbo].[TblHierarchy] ([ID], [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES (5, 5, N'Paan', 2, 2, N'Child of Man', NULL)

And this is the query to retrieve the info.

WITH CTE AS
(
    SELECT 
        H1.AgentID,
        H1.RootID,
        H1.AgentName,
        H1.Description [Parent],
        Description [Self Description],
        CAST(AgentID AS varbinary(MAX)) [Level],
        CAST (h1.AgentID AS varchar(max)) [LevelID],
        CAST (H1.IntroducerID AS varchar(max)) [IntroducerID]
    FROM 
        TblHierarchy H1
    WHERE 
        H1.RootID = 3

    UNION ALL 

    SELECT 
        H2.AgentID,
        H2.RootID,
        H2.AgentName,
        c.[Self Description],
        Description [Self Description],
        c.[Level]+CAST(h2.AgentID AS varbinary(MAX)) AS [Level] ,
        c.[LevelID] + '>' + CAST (h2.AgentID AS varchar(max)) [LevelID],
        CAST (H2.IntroducerID AS varchar(max)) [IntroducerID]
    FROM
        TblHierarchy H2
    INNER JOIN 
        CTE c ON h2.RootID = c.AgentID
)
SELECT *
FROM CTE 
CROSS APPLY 
    (SELECT 
         SUBSTRING(LevelID, 1, CHARINDEX('>', LevelID + '>') - 1) ) c(RootLevelID) 
ORDER BY 
    [Level] DESC
OPTION (MAXRECURSION 0)

Please provide the expected output fitting to the provided sample. Why are you casting to `VARBINARY(MAX)`? — Shnugo, Apr 18 '17 at 09:55

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

I do not fully understand your needs (especially what you are trying to achieve with all this casting...), but check this out:

EDIT: After some thinking I hope I got it

I removed my previous suggestion, where I had to change your input data. The following uses your input data unchanged and links via AgentID and RootID.

CREATE TABLE [dbo].[TblHierarchy](
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [AgentID] [bigint] NULL,
      [AgentName] [varchar](50) NULL,
      [RootID] [bigint] NULL,
      [IntroducerID] [bigint] NULL,
      [Description] [varchar](50) NULL,
      [HierarchyTree] [hierarchyid] NULL
      ) ON [PRIMARY];


INSERT [dbo].[TblHierarchy] ( [AgentID], [AgentName], [RootID], [IntroducerID], [Description], [HierarchyTree]) 
VALUES 
( 1, N'Toh', 0, NULL, N'', NULL)
,( 2, N'Man', 1, 0, N'Child of Toh', NULL)
,( 3, N'Rul', 1, 0, N'Child of Toh', NULL)
,( 4, N'Rafiq', 2, 2, N'Child of Man', NULL)
,( 5, N'Paan', 2, 2, N'Child of Man', NULL)

SELECT * FROM dbo.TblHierarchy;

WITH recCTE AS
(
    SELECT h.ID
          ,h.AgentID
          ,h.AgentName
          ,h.RootID
          ,h.IntroducerID
          ,h.[Description]
          ,CAST(h.[Description] AS VARCHAR(MAX)) AS HierDescr
    FROM dbo.TblHierarchy AS h WHERE h.IntroducerID IS NULL

    UNION ALL

    SELECT h.ID
          ,h.AgentID
          ,h.AgentName
          ,h.RootID
          ,h.IntroducerID
          ,h.[Description]
          ,r.HierDescr + '/' + h.[Description]
    FROM recCTE AS r
    INNER JOIN dbo.TblHierarchy AS h ON h.RootID=r.AgentID
)
SELECT * FROM recCTE
GO
DROP TABLE [dbo].[TblHierarchy];

Actually this is the logic that our client request. Introducer can register as many agent he wants under him, but in the binary tree structure can only have two child.Means, for the 3rd agent and the rest,will be going to sit anywhere as long it is still under the Introducer as the main root. Back to my issue, yeah just like you said, its a data problem. I found the problem already.Actually it is during the insertion where i insert more than 2 child on the same node. Anyway,thanks for point it out.Really appreacite it.. :) — user3127287, Apr 18 '17 at 10:31

SQL Server CTE statement Varbinary(MAX) Not Returning the RootID

1 Answers1

EDIT: After some thinking I hope I got it