1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
|
BEGIN TRAN
--Declare
DECLARE @Counter INTEGER
DECLARE @LoopMax INTEGER
DECLARE @ActualParentId BIGINT
DECLARE @ActualChildId BIGINT
DECLARE @ActualTreeL INTEGER
DECLARE @ActualTreeR INTEGER
DECLARE @ActualLevel INTEGER
--Init
SELECT @Counter = COUNT(*) FROM Est_Structure WHERE Est_HeaderId = 1
SET @ActualParentId = NULL
SET @ActualChildId = NULL
SET @ActualLevel = 0
SET @LoopMax = @Counter
--Reset All
UPDATE Est_Structure set TreeL=NULL, TreeR=NULL WHERE Est_HeaderId = 1
--Set root:
UPDATE Est_Structure SET TreeL = 1, Level = 0, TreeR = @Counter*2
WHERE Est_HeaderId = 1 AND ParentId IS NULL
SET @Counter = 0
WHILE EXISTS (SELECT NULL FROM Est_Structure WHERE Est_HeaderId = 1 AND TreeL IS NULL)
AND @Counter < @LoopMax
BEGIN
-- Loop - Begin
SET @Counter = @Counter + 1
SELECT TOP 1 @ActualChildId = Child.Est_StructureId, @ActualParentId = Child.ParentId
, @ActualTreeL = Parent.TreeL
, @ActualLevel = Parent.[Level]
FROM Est_Structure Child INNER JOIN Est_Structure Parent ON Parent.Est_StructureId = Child.ParentId
WHERE Child.Est_HeaderId = 1
AND Child.TreeL IS NULL
AND Child.ParentId IS NOT NULL
AND ( @ActualParentId IS NULL OR (@ActualParentId IS NOT NULL AND @ActualParentId = Child.ParentId))
ORDER BY Child.[Level], Child.[Type] DESC
--Get next value of TreeL...
SELECT @ActualTreeL = ISNULL(MAX(TreeL),1) FROM Est_Structure WHERE Est_HeaderId = 1 AND TreeL IS NOT NULL
SELECT @ActualTreeR = ISNULL(MAX(TreeR),0) FROM Est_Structure WHERE Est_HeaderId = 1 AND TreeR IS NOT NULL AND ParentId IS NOT NULL --AND TreeR < @LoopMax
SELECT @Error = @@ERROR, @Rowcount = @@ROWCOUNT
IF @ActualTreeR IS NOT NULL AND @ActualTreeR > @ActualTreeL
SET @ActualTreeL = @ActualTreeR
IF NOT EXISTS (SELECT NULL FROM Est_Structure WHERE Est_HeaderId = 1 AND ParentId = @ActualChildId)
BEGIN
--Feuilles... TreeR = TreeL + 1
UPDATE Est_Structure SET TreeL = @ActualTreeL+1, TreeR = @ActualTreeL+2
, [Level] = @ActualLevel+1
WHERE Est_StructureId = @ActualChildId
SET @ActualTreeL = @ActualTreeL + 2
END
ELSE
BEGIN
UPDATE Est_Structure SET TreeL = @ActualTreeL+1
, [Level] = @ActualLevel+1
WHERE Est_StructureId = @ActualChildId
SET @ActualTreeL = @ActualTreeL + 1
IF EXISTS (SELECT NULL FROM Est_Structure WHERE ParentId = @ActualChildId AND TreeL IS NULL)
BEGIN
PRINT 'Use ChildId as ActualParentId...'
UPDATE Est_Structure SET TreeR = @ActualTreeL+1 WHERE TreeR IS NULL AND Est_StructureId = @ActualParentId AND ParentId IS NOT NULL
IF @@ROWCOUNT > 0 SET @ActualTreeL = @ActualTreeL + 1
SET @ActualParentId = @ActualChildId
END
END
WHILE @ActualParentId IS NOT NULL
AND NOT EXISTS (SELECT NULL FROM Est_Structure WHERE Est_HeaderId = 1 AND TreeL IS NULL AND ParentId = @ActualParentId)
BEGIN
UPDATE Est_Structure SET TreeR = @ActualTreeL+1 WHERE TreeR IS NULL AND Est_StructureId = @ActualParentId
IF @@Rowcount > 0 SET @ActualTreeL = @ActualTreeL + 1
SELECT @ActualParentId = ParentId FROM Est_Structure WHERE Est_StructureId = @ActualParentId
END
-- Loop - End
END
--Results:
SELECT Est_StructureId, ParentId, Level, Rang, TreeL, TreeR, Type, BDE_NR, Bezug, Pos, Artikel
FROM Est_Structure
WHERE Est_HeaderId = 1
-- AND TreeL IS NOT NULL
-- AND (BDE_NR = 464915 OR BEZUG = 464915)
ORDER By TreeL, Rang, BDE_NR, Pos, [Level]
ROLLBACK |
Partager