Recusive Hierachy
-
I have two tables Account & AccountRelationship that look like this:
Account
ID
code
name
category
AccountRelationship
ParentAccount
ChildAccount
The Join between the Accounts table and AccountRelationship is on ID (not Code).
The relationship between accounts is stored in the AccountRelationship table. Accounts with no children are in the Accounts table, it is not stored as a ParentAccount with a Null Child.
I need to reconstruct the Hierachy in a denormalised form. Ie, Level1Code, Level1Name, Leve1Category, Level2Code, Level2Name, Level2Category etc, adding columns until I reach the end of the hierachy.
I have managed to do this with a series of temp tables and unions but I am looking for a better way.
Thanks,
Angela
-
I deal with this everday.
Temp tables and unions are the best methods. Cause you can debug easier and dynamically deal with a new level.
Mulitipal joins and the same table is very messy and a performance killer.
I do not use cursors.
With SQL Server 2005 it will be much easier to deal with as the T-SQL features PIVOT,UNPIVOT,RECUSIVE,OUTER APPLY AND CROSS APPLY query's.
I can wait to migrate
Andy. -
Hi Angela,
I have seen many posts of this kind on SSC so far. Usual suggestion is to solve the problem at the presentation level, not at the database level. I understand that having a stored procedure that works all that and returns a result set with unspecified and dynamically configured number of columns is a cool thing. Yet, it seems to me that it is not quite according to relational concepts and the purpose of SQL.
Regards,
Goce.
-
Hi Goce,
In this scenario it must been done at the database level because it is part of a larger processing requirement. The result set returned has to be further processed conditionally and eventually end up in an Analysis Services cube/s. This data is not being used for a report and the functionality I require cannot be done at the 'presentation level'
-
Angela,
Lookup "Expanding Hierarchies" in "Books Online"... has full explanation and an easily modifiable example script that could be changed to a stored procedure. That's one way to do this.
Another way is to nest tree's, in a much more set based fashion. Visit the following URL.
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=41059
I must warn you that there are some very bad errors in the code from that URL and you need to test each code snippet offered. Some will need a wholesale modification to get them to work, BUT the principle of the methods is definitely worth the trip and the code is salvagable.
One of the spots to watch for is in the last chunk of code (the thing that builds the "LFT" and "RGT" "Bowers") of the code is the following line...
WHILE counter <= (max_counter - 2)
It should be just WHILE counter <= max_counter or it will miss some the Bowers (the term "Bower" comes from ships and is one of the foward anchors. It is not listed in the article, that way.)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Does anyone have a good translation of Joe's code from the link above? I tried pulling it into QA and updating it. Since I don't quite follow the concepts I'm losing the functionality. I suppose I could post my code here and hope someone could point out my mistakes, but if anyone's already done a clean translation of it and would be willing to share it would be greatly appreciated.
-
Sure... with examples... I used temp tables for the example so I don't clutter up my hard drive... you don't have to (in fact, I recommend that you don't)...
--===== Suppress auto-display of line counts for appearance and speed
SET NOCOUNT ON
--===== If tempory tables for this test exist, drop them
IF OBJECT_ID('TempDB..#Personnel') IS NOT NULL
DROP TABLE #Personnel
IF OBJECT_ID('TempDB..#ScratchPad') IS NOT NULL
DROP TABLE #ScratchPad
IF OBJECT_ID('TempDB..#Tree') IS NOT NULL
DROP TABLE #Tree
--===== Create the #Personnel table to simulate a list of employees
CREATE TABLE #Personnel
(
Employee CHAR(10) NOT NULL PRIMARY KEY,
Boss CHAR(10) NULL,
Salary DECIMAL(6,2) NOT NULL
)
--===== Create the #ScratchPad table to temporarily hold data whil the process runs
CREATE TABLE #ScratchPad
(
Employee CHAR(10) NOT NULL,
Boss CHAR(10)
)
--===== Create the #Tree Table which will start empty
CREATE TABLE #Tree
(
Level INT NOT NULL,
Employee CHAR(10) NOT NULL,
LeftBower INT,
RightBower INT ,
Salary INT
)
--===== Declare Local Variables
DECLARE @MyCounter INT
DECLARE @MaxCount INT
DECLARE @Level INT
--===== Populate the #Personnel table to simulate a source data table
INSERT INTO #Personnel
(Employee, Boss, Salary)
SELECT 'Jim', Null, 1500 UNION ALL
SELECT 'Sally', 'Donna', 600 UNION ALL
SELECT 'Albert','Jim', 1000 UNION ALL
SELECT 'Bert', 'Albert', 900 UNION ALL
SELECT 'Chuck', 'Albert', 900 UNION ALL
SELECT 'Donna', 'Chuck', 800 UNION ALL
SELECT 'Eddie', 'Chuck', 700 UNION ALL
SELECT 'Fred', 'Chuck', 600 UNION ALL
SELECT 'Jeff', 'Jim', 1100 UNION ALL
SELECT 'Jacob', 'Jeff', 900 UNION ALL
SELECT 'Al', 'Jeff', 800
--===== Populate the #ScratchPad table from the data table
INSERT INTO #ScratchPad
(Employee, Boss)
SELECT Employee, Boss
FROM #Personnel
--======================================================================================
-- Build the tree
--======================================================================================
--===== Presets
SET @MyCounter = 2
SET @MaxCount = 2 * (SELECT COUNT(*) FROM #ScratchPad)
SET @Level = 1
--===== Put the number one dog into the tree
INSERT INTO #Tree
(Level, Employee,LeftBower,RightBower)
SELECT 1 AS Level,
Employee AS Employee,
1 AS LeftBower,
NULL AS RightBower --Will be determined later
FROM #ScratchPad
WHERE Boss IS NULL
--===== Build the tree
WHILE @MyCounter <= (@MaxCount)
BEGIN
IF EXISTS ( -- See if anything left to do at this level
SELECT *
FROM #Tree AS t,
#ScratchPad AS s
WHERE t.Employee = s.Boss
AND t.Level = @Level
)
BEGIN --===== Push when Level has subordinates, set LeftBower value
INSERT INTO #Tree
(Level,Employee,LeftBower,RightBower)
SELECT (@Level + 1) AS Level,
MIN(s.Employee) AS Employee,
@MyCounter AS LeftBower,
NULL AS RightBower --Will be determined on the way back up
FROM #Tree AS t,
#ScratchPad AS s
WHERE t.Employee = s.Boss
AND t.Level = @Level
--===== Delete each item inserted in #Tree
DELETE FROM #ScratchPad
WHERE Employee = (SELECT Employee
FROM #Tree
WHERE Level = @Level + 1)
--===== Update the counters for the next item down
SET @MyCounter = @MyCounter + 1
SET @Level = @Level + 1
END
ELSE
BEGIN --===== Pop the #Tree and set RightBower value
UPDATE #Tree
SET RightBower = @MyCounter,
Level = -Level -- pops the #Tree
WHERE Level = @Level
--===== Update the counters for the next item up
SET @MyCounter = @MyCounter + 1
SET @Level = @Level - 1
END
END --WHILE
--===== Update the #Tree table Level for positive numbers
-- If any negatives continue to exist, then big problem
UPDATE #Tree
SET Level = -Level
--===== Enter the salaries into the tree
UPDATE #Tree
SET Salary = p.Salary
FROM #Tree t,
#Personnel p
WHERE t.Employee = p.Employee
--===== Display the results
PRINT '===== #ScratchPad table ====='
SELECT * FROM #ScratchPad
PRINT '===== #Tree table ====='
SELECT * FROM #Tree
--===== Find ALL of Chuck's upline including Chuck
PRINT '===== Chuck''s Upline ====='
SELECT t2.*
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
AND t1.Employee = 'Chuck'
ORDER BY t1.LeftBower
--===== Find ALL of Chuck's downline including Chuck
PRINT '===== Chuck''s Downline ====='
SELECT t1.*
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
AND t2.Employee = 'Chuck'
ORDER BY t2.LeftBower
--===== Find ALL of Jim's downline including Jim
PRINT '===== Jim''s Downline ====='
SELECT t1.*
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
AND t2.Employee = 'Jim'
ORDER BY t2.LeftBower
--===== Find ALL of Jim's downline including Jim for 3 levels
PRINT '===== Jim''s Downline 3 levels ====='
SELECT t1.*
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
AND t2.Employee = 'Jim'
AND t1.Level < t2.Level + 3
ORDER BY t2.LeftBower
--===== Find ALL of Jim's downline including Jim for 3 levels
PRINT '===== Jeff''s Downline 3 levels ====='
SELECT t1.*
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
AND t2.Employee = 'Jeff'
AND t1.Level < t2.Level + 3
ORDER BY t2.LeftBower
--===== Find all of Fred's upline including Fred
PRINT '===== Fred''s Upline ====='
SELECT t2.*
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
AND t1.Employee = 'FRED'
ORDER BY t1.LeftBower
--===== Find all of Fred's upline including Fred for 3 levels
PRINT '===== Fred''s Upline 3 levels ====='
SELECT t2.*
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
AND t1.Employee = 'FRED'
AND t2.Level > t1.Level - 3
ORDER BY t1.LeftBower
--===== Find the total salary each person controls
PRINT '===== Total salary each person controls ====='
SELECT t2.Employee, SUM(ISNULL(t1.Salary,0)) TotalControlledSalary
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
AND t1.Employee = t1.Employee
GROUP BY t2.Employee
----------------------------------------------------------------------
--===== Add a new employee to a boss' downline
DECLARE @right_most_sibling INT
DECLARE @MyLevel INT
--===== Get the RightBower from the new employee's immediate boss
-- and assign a level of just one more than the boss.
SELECT @right_most_sibling = RightBower,
@MyLevel = Level + 1
FROM #Tree
WHERE Employee = 'Donna'
--===== Update everone to the right of the new employee by adding 2
-- to both bowers (1 for each direction/bower for new employee)
UPDATE #Tree
SET LeftBower = CASE
WHEN LeftBower > @right_most_sibling
THEN LeftBower + 2
ELSE LeftBower
END,
RightBower = CASE
WHEN RightBower >= @right_most_sibling
THEN RightBower + 2
ELSE RightBower
END
WHERE RightBower >= @right_most_sibling
--===== Now that there's room, insert the new employee. Since the
-- new employee has no subordinates, yet, the right bower will
-- always be always be just 1 more than the left. The new
-- employee will have a left bower of what the right bower of
-- the boss used to be.
INSERT INTO #Tree (Level,Employee, LeftBower, RightBower)
VALUES (@MyLevel,'New Guy',@right_most_sibling,(@right_most_sibling + 1))
--Show the insert
--===== Find ALL of Jim's downline including Jim
PRINT '===== Jim''s Downline ====='
SELECT t1.*
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
AND t2.Employee = 'Jim'
ORDER BY t1.LeftBower
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Looks like that did the trick!
Thanks!!!
-
Jeff,
I'm getting some odd results when I really digest the output from this. According to Joe's model:
Albert's LeftBower is 1 and his RightBower is 1 more than the highest value of all his subordinates. Same for Chuck. Anyone who is a "Leaf" node has a RightBower that is their LeftBower+1.
The code you posted gets everyone in the right sequence by LeftBower, but their RightBowers are all foobar'd. The top guy is (1,556), his first sub is (2,557), his first sub is (3,558) even though he has no subs. Shouldn't he be (3,4) in Joe's model?
Here's a list of the first few entries:
1 225663 1 556 Wolin,Neal S.
2 110219 2 557 Malchodi Jr.,William B.
3 114068 3 558 Majeski,Stephen L.
3 121417 5 558 Tedesco Jr.,Joseph W.
4 123836 6 559 Nardella,Diana
3 121865 9 558 Cubanski,James
4 113782 10 559 Caputo,Mary E.
5 118544 11 560 Colla,Wayne R.
6 111302 12 561 Hunt,Lynda H.
6 112470 14 561 Stachelek,Linda M.
The Left's make sense to me, the right's seem odd. Wolin is the top guy so his Right should be the highest. Malchodi's right should be <= Wolin's right. Majeski has no sub's, so his right should be his left+1.
Or am I missing something? I know Joe Celko passes through here occasionally. Maybe he can weigh in on this...
-
Obviously, the code I posted had to be changed to accomodate your particular requirements. You are correct... something went drastically wrong. If you could post the schema of your personnel table, some sample data (preferably for the entries you posted), the tree build code you used, and the schema for the tree, I'd be happy to take a look.
Here's the contents of the "Tree" table from the code I posted...
===== #Tree table =====
Level Employee LeftBower RightBower Salary
----------- ---------- ----------- ----------- -----------
1 Jim 1 22 1500
2 Albert 2 15 1000
3 Bert 3 4 900
3 Chuck 5 14 900
4 Donna 6 9 800
5 Sally 7 8 600
4 Eddie 10 11 700
4 Fred 12 13 600
2 Jeff 16 21 1100
3 Al 17 18 800
3 Jacob 19 20 900
As you surmised, no number in the combination of Left and Right Bowers should ever be repeated.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Actually I didn't have to change much. Took out the Salary piece of the tables and loaded the #Personnel from my database's Employees table. Other than that it should be the same unless I fat-fingered something and didn't find it when I went back to review...
--===== Suppress auto-display of line counts for appearance and speed
SET NOCOUNT ON
--===== If tempory tables for this test exist, drop them
IF OBJECT_ID('TempDB..#Personnel') IS NOT NULL
DROP TABLE #Personnel
IF OBJECT_ID('TempDB..#ScratchPad') IS NOT NULL
DROP TABLE #ScratchPad
IF OBJECT_ID('TempDB..#Tree') IS NOT NULL
DROP TABLE #Tree
--===== Create the #Personnel table to simulate a list of employees
CREATE TABLE #Personnel
(
Employee VARCHAR(10) NOT NULL PRIMARY KEY,
Boss VARCHAR(10) NULL
)
--===== Create the #ScratchPad table to temporarily hold data whil the process runs
CREATE TABLE #ScratchPad
(
Employee VARCHAR(10) NOT NULL,
Boss VARCHAR(10)
)
--===== Create the #Tree Table which will start empty
CREATE TABLE #Tree
(
Level INT NOT NULL,
Employee VARCHAR(10) NOT NULL,
LeftBower INT,
RightBower INT
)
--===== Declare Local Variables
DECLARE @MyCounter INT
DECLARE @MaxCount INT
DECLARE @Level INT
--===== Populate the #Personnel table to simulate a source data table
INSERT INTO #Personnel (Employee, Boss) Values('225663', NULL) -- Neil
INSERT INTO #Personnel -- Neils subs
Select [ID], Supv_ID from Employees
Where Supv_ID is not NULL AND Status <> 2 --All with manager and active
--===== Populate the #ScratchPad table from the #Personnel data table
INSERT INTO #ScratchPad
SELECT Employee, Boss FROM #Personnel
--======================================================================================
-- Build the tree
--======================================================================================
--===== Presets
SET @MyCounter = 2
SET @MaxCount = 2 * (SELECT COUNT(*) FROM #ScratchPad)
SET @Level = 1
--===== Put the number one dog into the tree
INSERT INTO #Tree
(Level, Employee,LeftBower,RightBower)
SELECT 1 AS Level,
Employee AS Employee,
1 AS LeftBower,
NULL AS RightBower --Will be determined later
FROM #ScratchPad
WHERE Boss IS NULL
--===== Build the tree
WHILE @MyCounter <= (@MaxCount)
BEGIN
IF EXISTS ( -- See if anything left to do at this level
SELECT *
FROM #Tree AS t,
#ScratchPad AS s
WHERE t.Employee = s.Boss
AND t.Level = @Level
)
BEGIN --===== Push when Level has subordinates, set LeftBower value
INSERT INTO #Tree
(Level,Employee,LeftBower,RightBower)
SELECT (@Level + 1) AS Level,
MIN(s.Employee) AS Employee,
@MyCounter AS LeftBower,
NULL AS RightBower --Will be determined on the way back up
FROM #Tree AS t,
#ScratchPad AS s
WHERE t.Employee = s.Boss
AND t.Level = @Level
--===== Delete each item inserted in #Tree
DELETE FROM #ScratchPad
WHERE Employee = (SELECT Employee
FROM #Tree
WHERE Level = @Level + 1)
--===== Update the counters for the next item down
SET @MyCounter = @MyCounter + 1
SET @Level = @Level + 1
END
ELSE
BEGIN --===== Pop the #Tree and set RightBower value
UPDATE #Tree
SET RightBower = @MyCounter,
Level = -Level -- pops the #Tree
WHERE Level = @Level
--===== Update the counters for the next item up
SET @MyCounter = @MyCounter + 1
SET @Level = @Level - 1
END
END --WHILE
--===== Update the #Tree table Level for positive numbers
-- If any negatives continue to exist, then big problem
UPDATE #Tree
SET Level = -Level
Where Level < 0
--===== Display the results
-- PRINT '===== #ScratchPad table ====='
-- SELECT * FROM #ScratchPad
-- PRINT '===== #Tree table ====='
-- SELECT * FROM #Tree
--===== Find ALL of Neil's downline including Neil
SELECT t1.*, space(t1.level*2)+d.cn as [Name], E.tierint
FROM #Tree AS t1 left outer join #Tree AS t2
on t1.LeftBower BETWEEN t2.LeftBower AND t2.RightBower
left outer join Directory D
on t1.employee=d.employeeid
left outer join Employees E
on t1.employee=E.ID
Where t2.Employee = '225663'
ORDER BY t2.LeftBower, t2.rightbower --, tierint
-
rschaeferhig,
You were quite right... you didn't change much. In fact, when I built a test Directory and Employees table and inserted the records you showed in a previous post (sans the bowers, of course), I couldn't get the code you posted to fail. Then, I tried adding a person to the Employees table that already existed but just had a different boss (used the same status code of <> 2). Of course, the Primary Key on the #Personnel table kept that eventuality from loading from the Employees table into the #Personnel table (Primary Key violation).
Then, I added a person to the Directory table that already existed (duplicated the EmployeeID)... that's when I started getting dupes on the bowers but only in the "Neil's Downline" output. The content of the #Tree table was OK.
So, I think there could be duplicate EmployeeID's in the Directory table... not sure because I don't have access to your data. Try this to find out...
- SELECT EmployeeID, COUNT(*)
- FROM Directory
- GROUP BY EmployeeID
- HAVING COUNT(*) > 1
That should find any dupes you may have... of course, if you have a Primary Key or Unique key on the EmployeeID column of the Directory table, you don't even need to do that check. That would also mean that something else is wrong. That would lead us back to the Employees table...
When your code loads the Employees table into the #Personnel table, you use "AND Status <> 2" as part of the criteria. In the code that tries to find Neil's downline, you don't include that filter. That does leave the possiblity that there might actually be dupes in the Employees table in the ID column and we need to check there, as well...
- SELECT ID, COUNT(*)
- FROM Employees
- GROUP BY ID
- HAVING COUNT(*) > 1
If you find dupes in the Employees table (by ID), then you'll need to add the "AND Status <> 2" as part of the criteria in the code that tries to find Neil's downline. When I added a dupe employee with a status of 2, the tree build still worked correctly but the code to find Neil's downline went crazy with apparent dupe bowers because there was no longer a 1 to 1 relationship between t1 and Employees.
Of course, if you don't find dupes in either table, please confirm whether or not there are any dupe bowers in the #Tree table. You shouldn't find any but we need to check anyway...
SELECT d.Bower,COUNT(*)
FROM
(
SELECT LeftBower AS Bower
FROM #Tree
UNION ALL
SELECT RightBower AS Bower
FROM #Tree
) d
GROUP BY d.Bower
HAVING COUNT(*) > 1
If dupes are found in the #Tree table and not in either the Directory or Employees table, it'll be back to the drawing board on the code but, again, I haven't been able to make the Tree Build code fail. I would really be suprised if there were no dupes in either Directory (EmployeeID) or Employees (ID) tables (column).
Couple of other things...
1. Although the implicit conversions from INT to CHAR(10) for most of the ID's work just fine, you may want to change the DDL for the temp tables from CHAR(10) to INT for things like EmployeeID and Boss to get that extra bit of speed out of the thing.
2. Although it produces the exact same output as your code, the following code is a bit easier to read for troubleshooting purposes because the self join to the #Tree table is isolated as a derived table and the outer joins have been eliminated. Of course, elimination of the outer joins only works if you have some form of referential integrity that ensures all 3 of your tables have a valid entry for each employee...
--===== Find ALL of Neil's dowline including Neil and then
-- add extra info for each person.
SELECT dt.*, SPACE(dt.[Level]*2) + d.CN AS [Name], e.TierInt
FROM #Directory d,
#Employees e,
(--Derived table "dt" finds the correct downline. Output
--of this joins with other tables to get the rest of the info.
SELECT t1.[Level],t1.Employee,t1.LeftBower,t1.RightBower
FROM #Tree AS t1,
#Tree AS t2
WHERE t1.LeftBower BETWEEN t2.LeftBower and t2.RightBower
AND t2.Employee = '225663'
)dt
WHERE d.EmployeeId = dt.Employee
AND e.ID = dt.Employee
Lemme know how the dupe checks come out.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
There are no dupes in Employees or Directory. The ID is our corporate Employee ID, there can only be one. It is the Primary Key in both tables.
The "Status<>2" applies only to the Employees table to eliminate "Inactive" (terminated) employees. I don't need it to get Neil's downline since the LEFT OUTER JOIN of Directory to #Tree limits the Directory entries to those in #Tree which was built with only active employees.
I can't change ID to INT because some ID's have characters. It just so happens that the ones you saw didn't.
I changed the check for duplicate bowers in the #Tree to:
Select LeftBower as Lft, count(*) as Bowers
from #Tree
Group by LeftBower
Having Count(*)>1
Select RightBower as Rgt, count(*) as Bowers
from #Tree
Group by RightBower
Having Count(*)>1
I got no duplicates on LeftBower (expected), but the following on RightBower:
- Rgt Bowers
- 561 9
562 43
563 102
564 97
565 27
It appears that the code that pops back up the tree is somehow getting confused.
If we could take this offline I could send you the entire #Tree to look at, even take a snapshot before the bowers are set. You could run it through the part of the code that sets the bowers and maybe figure out where the duplicate RightBowers are coming from. There are only 270+- entries so I could dump the tree into a CSV file and zip it up. Send me a PM with your email if this is OK. I've been beating on this hierarchy report for a couple of weeks and my boss is starting to get antsy. (I'd give anything for SQL 2005 and Recursive Functions with CTE's).
Thanks for all your help.
-
I was looking at the "pop" code and trying to figure out what it was doing. Shouldn't the RightBower for any entry without subordinates be LeftBower+1? Shouldn't the RightBower for anyone with subordinates be Max(Subordinates RightBower)+1? I don't understand how your code does that, if that's what your trying to do?
Or do I not understand the original model?
-
Thanks Joe,
The problem is in changing the code for converting an adjacency model to a nested set model from SQL/PSM to MS TSQL. There are several constructs in your code that don't directly translate to MS TSQL.
I will gladly buy your book if it has that code in MS TSQL!!
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply