How to get the root node in a table

Question

Post reply

How to get the root node in a table

halifaxdal

SSCoach

Points: 19741
More actions
August 12, 2009 at 12:42 pm

#385168

Hi folks,
I have a table called RSUNew, structure as follows:
ID, OrgUnitID
data like:
1 OU00000055
2 OU00000056
3 OU00000057
4 OU00000058
5 OU00000059
6 OU00000050
...
I have another table called ISC_OrgUnits, structured as follows:
OrgUnitID, Parent
data like:
OU0000001 OU0000002
OU0000002 OU0000003
OU0000003 OU0000004
OU0000004 OU0000005
...
The meaning is straight forward, OU0000001's parent is OU0000002, and so on
Now I need to write a function, with a given OrgUnitID, I need to find out what's the root OrgUnitID I can trace back in RSUNew table.
I have the following function, it is not working but I just copy here for your reference.
Thank you very much.
ALTER FUNCTION [dbo].[fnGetTopOrgUnitIDInRSU] (@OrgUnitID varchar(20)) returns varchar(20) AS
Begin
DECLARE @ID varchar(20)
Declare @ret varchar(20)
set @ret = @OrgUnitID
IF @OrgUnitID not in (select OrgUnitID from RSUNew)
Begin
--Get the parent OrgUnitID
SELECT @ID = parent from ISC_OrgUnits where OrgUnitID = @OrgUnitID
set @ret = @ID
IF @ID not in (select OrgUnitID from RSUNew)
begin
EXEC @ret = dbo.fnGetTopOrgUnitIDInRSU @ID
RETURN @ret
end
RETURN @ret
End
RETURN @ret
end
🙂

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I think that what you really want is a recursive View, like this:

CREATE VIEW OrgLevels AS

WITH cteRecur AS (

SELECT r.OrgUnitID, o.Parent, 1 as Lvl

FROM RSUNew r

LEFT JOIN ISC_OrgUnits o ON r.OrgUnitID = o.OrgUnitID

WHERE o.OrgUnitID is null

UNION ALL

SELECT r.OrgUnitID, o.Parent, c.Lvl+1 as Lvl

FROM RSUNew r

JOIN ISC_OrgUnits o ON r.OrgUnitID = o.OrgUnitID

JOIN cteRecur c ON c.OrgUnitID = o.Parent

)

SELECT *

FROM cteRecur

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Thank you Barry,

But I do need a Scalar-Valued Function, I need to call the function in other function/stored procedure. Can you help me that?

FUNCTION [dbo].[fnGetTopOrgUnitIDInRSU] (@OrgUnitID varchar(20)) returns varchar(20)

Thanks in advance.

Raymond

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

You can call a view from any where in SQL, why wouldn't you want that?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Here's a correction to my View that should help:

CREATE VIEW OrgLevels AS

WITH cteRecur AS (

SELECT r.OrgUnitID, o.Parent, 1 as Lvl, r.OrgUnitID as TopUnit

FROM RSUNew r

LEFT JOIN ISC_OrgUnits o ON r.OrgUnitID = o.OrgUnitID

WHERE o.OrgUnitID is null

UNION ALL

SELECT r.OrgUnitID, o.Parent, c.Lvl+1 as Lvl, c.TopUnit

FROM RSUNew r

JOIN ISC_OrgUnits o ON r.OrgUnitID = o.OrgUnitID

JOIN cteRecur c ON c.OrgUnitID = o.Parent

)

SELECT *

FROM cteRecur

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

And here's how to use a View like a function:

SELECT

*

, (SELECT TopUnit FROM OrgLevels WHERE OrgUnitID = @OrgUnitID) as TopUnit

FROM YourTable

WHERE ...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

halifaxdal SSCoach Points: 19741 More actions · Answer 6

Thanks Barry,

This is my first time using recursive view, the result doesn't seem to be reasonable, 'cause I have 60773 nodes in ISC_OrgUnits and 23 in RSUNew, presumably the view should have 60773 records, currently the view returns only 24 unique records.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Hmm, sorry. I will try to test and fix it tonight.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

halifaxdal SSCoach Points: 19741 More actions · Answer 8

Thank you Barry.

I am still wondering if a function is possible?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

A function is possible but a recursive function is not recommended. If really you wanted to do a function then I would recommend that it be written to wrap a recursive CTE query or view like mine (that works right, of course).

Functions have a lot of overhead (except for ITVF's: In-line Table-Valued Functions) , and recursive functions just compound that problem.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

OK here's the latest plus the scripts to test and demonstrate that it works correctly:

drop table RSUNew

go

drop table ISC_OrgUnits

go

create table RSUNew(id int, OrgUnitID varchar(12));

go

create table ISC_OrgUnits(OrgUnitID varchar(12), Parent varchar(12));

go

insert into RSUNew select 1, 'OU0000001'

insert into RSUNew select 2, 'OU0000002'

insert into RSUNew select 3, 'OU0000003'

insert into RSUNew select 4, 'OU0000004'

insert into RSUNew select 5, 'OU0000005'

insert into RSUNew select 6, 'OU0000006'

insert into RSUNew select 11, 'OU0000055'

insert into RSUNew select 12, 'OU0000056'

insert into RSUNew select 13, 'OU0000057'

insert into RSUNew select 14, 'OU0000058'

insert into RSUNew select 15, 'OU0000059'

insert into RSUNew select 16, 'OU0000050'

go

insert into ISC_OrgUnits select 'OU0000001', 'OU0000055'

insert into ISC_OrgUnits select 'OU0000002', 'OU0000003'

insert into ISC_OrgUnits select 'OU0000003', 'OU0000004'

insert into ISC_OrgUnits select 'OU0000004', 'OU0000005'

insert into ISC_OrgUnits select 'OU0000056', 'OU0000055'

insert into ISC_OrgUnits select 'OU0000058', 'OU0000057'

insert into ISC_OrgUnits select 'OU0000059', 'OU0000050'

go

drop VIEW OrgLevels

go

CREATE VIEW OrgLevels AS

WITH cteRecur AS (

SELECT r.OrgUnitID, o.Parent, 1 as Lvl, r.OrgUnitID as TopUnit

FROM RSUNew r

LEFT JOIN ISC_OrgUnits o ON r.OrgUnitID = o.OrgUnitID

WHERE o.OrgUnitID is null

UNION ALL

SELECT r.OrgUnitID, o.Parent, c.Lvl+1 as Lvl, c.TopUnit

FROM RSUNew r

JOIN ISC_OrgUnits o ON r.OrgUnitID = o.OrgUnitID

JOIN cteRecur c ON c.OrgUnitID = o.Parent

)

SELECT *

FROM cteRecur;

go

select * from OrgLEvels

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

halifaxdal SSCoach Points: 19741 More actions · Answer 11

Hi Barry,

Thank you very much for your time. I have just pull out the real data as spreadsheet (see the attachment). I don't see a positive result in your recursive view, maybe it will be easier for you to test on the real data.

And let me recap the need here again: for all the OrgUnitID in table ISC_OrgUnits, there is a correspondent OrgUnitID for it in table RSUNew (after you trace it back by parent and parent), I need to find out that.

Thanks again.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

halifaxdal (8/17/2009)[hr...I don't see a positive result in your recursive view....

Please explain what this means.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

halifaxdal SSCoach Points: 19741 More actions · Answer 13

Hi Barry,

I mean using your recursive view it is still returning only very few records (24 in total), for a records number of 4000 in ISC_OrgUnits, I should be able to expect 4000 records in the view, right?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

What nodes are being returned?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]