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)
I do not fully understand your needs (especially what you are trying to achieve with all this casting...), but check this out:
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];