October 10, 2007 at 3:35 pm
Converting a FoxPro Procedure to SQL
I have a standard adjacency matrix recursive table.
XOrder Child Parent
1 100 NULL
2 101 100
3 102 100
4 103 100
5 104 102
6 105 102
7 106 NULL
8 107 106
The problem is the rows are in a random order and XOrder is not populated. I need to populate XOrder such that when the table is ordered by XOrder it will be in the order above.
I originally did this in FoxPro. Being able to SCAN through records in a table made it easy. SQL however is a different story. What I did in Fox was:
1.Indexed on XOrder
2.Set XOrder to 1 for all Parent = NULL
3.This placed all the parents at the top of the file.
4.This is where Fox has a SCAN command that would sequentially step through the file from top to bottom. Starting from the top of the file.
5.Find the children of this row.
6.Set their XOrder to the current XOrder+.5. This placed the children under the parent.
7.Sequentially number all remaining records starting from the XOrder of the current record.
8.Now the current record and the record immediately below it have the correct XOrder .
9.Next SKIP to the next record
10.Replete from step 5. This would be the end of the Fox SCAN loop and would automatically repeat until it reached the end of the table then exited the loop.
This will correctly set XOrder for the entire table.
The problem is there is no SCAN or SKIP in SQL. How would I accomplish this in SQL?
October 11, 2007 at 8:26 pm
Search for "hierarchy" on this forum... also lookup "expanding hierarchies" in Books Online. Because you're using 2k5, you may want to add "CTE" to your search criteria.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2007 at 4:38 am
You could create an SP with a cursor on the table,
and for each row determine the order number as:
if parent is null, then the xorder would be the amount of records that have a lower childnumber + 1,
if parent is not null, then the xorder would be the xorder no of the parent plus the amount of records with the same parent which have a smaller child plus one.
so something like:
create procedure populateXORder
as
begin
declare @parent int,
@child int
declare cursor c_xorder local read_only fast_forward
for
select child, parent
from table
open c_xorder
fetch next from c_xorder
into @child, @parent
while (@@fetch_status <> -1)
begin
if @parent is null
begin
update t set xorder = (select count (*) from table t1 where t1.child < t.chid) + 1
from table t
where t.child = @child
end
else
begin
update t set xorder = tp.xorder + (select count (*) from table t1 where t1.parent = t.parent and t1.child < t.chid) + 1
from table t
join table tp
on t.parent = tp.child
where t.child = @child
end
end
end
jou probably have to run the sp multiple times before every row is ordered
Willem
October 12, 2007 at 7:34 am
No, no.... the overhead of a cursor is not required here. Take my previous suggestion and find out how to build a recursive CTE to resolve these types of hierarchies.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2007 at 8:05 am
Thank you very much for your inputs. I did some research based on the comments here. I found using Recursive Queries Using Common Table Expressions the most efficient solution.
-- Multi branch tree in an Adjacency Matrix modle table.
DECLARE @Employee TABLE(ManagerID INT, EmployeeID INT)
INSERT @Employee
SELECT Null, 100
UNION ALL SELECT 100, 101
UNION ALL SELECT 100, 102
UNION ALL SELECT Null, 200
UNION ALL SELECT 200, 201
UNION ALL SELECT 200, 202 ;
WITH DirectReports
AS
(
-- Anchor member definition
SELECT
e.ManagerID,
e.EmployeeID,
0 AS Level
FROM
@Employee AS e
WHERE
ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT
e.ManagerID,
e.EmployeeID,
Level + 1
FROM
@Employee AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT
*
FROM
DirectReports
ORDER BY
EmployeeID,
ManagerID
October 12, 2007 at 6:03 pm
Now, that's what I'm talking about! Nice job, Kirk!! And, thanks for posting your solution! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply