I consulted many books, articles, posts on trees and hierarchies realized in SQL (databases)
And all of them use ParentID for linking but not ChildID.
Why is it?
What are possible pitfall, catch33 in basing the trees/hierarchies on linking through ChildID instead of ParentID?
I have this question is the extension of my previous question.I have a table,
DECLARE @TBL TABLE (RowNum INT, DataId int, RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 1.00, NULL),
(2, 105717, NULL, NULL),
(3, 105718, NULL, NULL),
(4, 105509, 2.00, NULL),
(5, 105510, 3.00, NULL),
(6, 105514, NULL, NULL),
(7, 105513, 4.00, NULL),
(8, 105719, NULL, NULL),
(9, 105718, NULL, NULL),
(10, 105718, NULL, NULL)
SELECT * FROM @TBL
Initially I asked question related to update "RowOrder", now I need to update "ParentId" also. example : if master has RowOrder 1.00 and there are 2 child's before 2.00 (another master) , then child entites RowOrder will be 1.01 & 1.02 & ParentId for both should be = 105508 and so on...
I got below query to update "RowOrder" from another thread,;
with tmp(Original_RowNum, Original_DataId, Original_RowOrder,New_RowOrder) as
(
select RowNum, DataId, RowOrder,cast(RowOrder as DECIMAL(18,2)) from @TBL where ROWNum = 1
union all
select t.RowNum, t.DataId, t.RowOrder,case when t.RowOrder is null then cast(tmp.New_RowOrder + 0.01 as DECIMAL(18,2)) else cast(t.RowOrder as DECIMAL(18,2)) end from @TBL t inner join tmp on tmp.Original_RowNum + 1 = t.RowNum
)
select * from tmp
how to update "ParentId" for table @TBL.
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....