April 24, 2003 at 5:00 am
I have a hierarchial issue in a table on SQL 2000 server.
See table below.
ProjID ProjectName parentID
1 Daffyduck 0
3 MickeyMouse 1
4 Bulldog 3
11 Bulldog - Comms 4
12 Bulldog Migrat 4
136 Babydog 4
146 Bulldog UIQ 4
5 Birdie 3
14 Birdie Defect 5
125 Birdie2 14
153 Birdie Perform 14
55 tweatybird 5
61 tweatybird - 55
81 tweatybird 2.0 55
56 Birdie Rubix 5
I want to create a stored proc and create a parameter called Project. So that when I
say select as my parameter Birdie (project Name) with Project ID 5 that it will include all its subprojects as well, ie. 5 Birdie 3
14 Birdie Defect 5
125 Birdie2 14
153 Birdie Perform 14
55 tweatybird 5
61 tweatybird - 55
81 tweatybird 2.0 55
56 Birdie Rubix 5
The ProjectID , ProjectName and ParentID all reside on the same table. There is a link between the ProjectID and the ParentID fields. At the moment when i choose Birdie with ProjectID 5 it ONLY includes subprojects with PARENTID 5 and excludes the rest !!!
HELP !!
How can i get this to work ???
greatly appreciate if you could help me out with this query ???????
April 24, 2003 at 7:23 am
One way is to use recursive functions
Sql*server 2000 supports user defined functions.
You can create a function fn_IsValidChild with two parameters projid and parentid. This finds the parents for projid in the hierarchy and returns 1 if it matches the parent requested.
The following sql returns all sub projects for parent 5.
SELECT ProjID,*
FROM projects
WHERE fn_IsValidChild(ProjID,5) = 1
GO
--The sample function is below
CREATE FUNCTION fn_isvalidchild(@p_Proj_id INT, @p_parent_id int)
RETURNS int
AS
BEGIN
DECLARE @m_cur_parent_id INT
SELECT @m_cur_parent_id = parent_id
FROM projects
WHERE ProjId = @p_Proj_id
IF @m_cur_parent_id = @p_parent_id
RETURN 1 -- This is valid child
IF @m_pid = 0 -- Top of hierachy, parent does not match
RETURN 0
--Try it again with next parent in hierarchy
SELECT @m_ret = fn_isvalidchild(@m_cur_parent_id, @p_parent_id)
RETURN @m_ret
END
GO
--Note that this is just an example. I could not test this as I do not have sql 2000
April 24, 2003 at 10:47 am
cheers for that
but
i'm gettting the following error:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
April 24, 2003 at 11:32 am
I have a lot of experience with recursive hierarchies and believe I have come up with a good solution. The implementation will require an extra field or 2 in the table and a little more code after a record has been added, but reading the records later and filtering out just a sub-section of the hierarchy is a breeze!
First of all, add 3 fields to your table: AncestryList, SiblingOrder, TreeOrder.
The SiblingOrder field is optional, but will allow you to later change the display order of the fields within the hierarchy (aka tree). The TreeOrder field will be a calculated field and allows easy sorting of the entire tree or a sub-section (branch).
The AncestryList field is the workhorse. This varchar field is calculated solely from the Key field in the table (ProjID in your case). It will contain the key fields for all parent records down to the current record. Let's use your fields as an example.
ProjID, ProjectName, ParentID, AncestryList
1 Daffyduck 0 +-1-
3 MickeyMouse 1 +-1-3-
4 Bulldog 3 +-1-3-4-
11 Bulldog - Comms 4 +-1-3-4-11-
12 Bulldog Migrat 4 +-1-3-4-12-
136 Babydog 4 +-1-3-4-136
5 Birdie 3 +1-3-5-
14 Birdie Defect 5 +-1-3-5-14-
125 Birdie2 14 +-1-3-5-14-125-
153 Birdie Perform 14 +-1-3-5-14-153-
If you want to get just the branch of the hierarchy starting with Birdie (ID=5), use the following:
Select * from table where AncestryList like '+1-3-5-%'
This will return all records whose AncestryList starts with the given string which is the AncestryList of Birdie, so you can see how easy it is to get branches.
If you are interested in going this route, just ask and I'll be happy to post actual code that I use to auto-populate the TreeOrder and AncestryList fields.
🙂
April 25, 2003 at 1:11 am
I agree with Darren. If it is possible to create ancestry list column easily then this is certainly better idea as this involves less number of selects compared to other one.
Regarding your query on neting level error, this can be avoided using loop to replace recursion as follows
drop function dbo.fn_IsValidChild
GO
CREATE FUNCTION dbo.fn_IsValidChild
(
@p_Proj_id INT,
@p_parent_id INT
)
RETURNS INT
AS
BEGIN
----------------------------------------------------
--This is a recursive function which traverses
--through the hierarchy and decides parent child
--relationship
----------------------------------------------------
DECLARE @m_cur_parent_id INT --To store current Project Id
DECLARE @m_ret INT --To store recursive value
--If this is parent then return true
If @p_Proj_id = @p_parent_id
RETURN 1
WHILE 1 = 1
BEGIN
--Get Parent for current Project
SELECT @m_cur_parent_id = parent_id
FROM projects
WHERE Project_id = @p_Proj_id
--Current record does not exists
IF @m_cur_parent_id IS NULL
RETURN 0
--Current record is direct child of requested Parent
IF @m_cur_parent_id = @p_parent_id
RETURN 1 -- This is valid child
--Top of hierachy, parent does not match
IF @m_cur_parent_id = 0
RETURN 0
SET @p_Proj_id = @m_cur_parent_id
END
RETURN 0
END
GO
Hope this helps
April 25, 2003 at 6:37 am
cheers for that guys
it worked !!!
thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply