August 17, 2009 at 10:20 am
OH, I see, RSUNew only contains the root notes. That's the 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]
August 17, 2009 at 10:23 am
Try it like this instead (untested, I have to run out to a customer site ...):
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 o.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
(edit: fixed bug in code)
[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]
August 17, 2009 at 12:25 pm
Hi Barry,
I would rather wait until you test it, the code hangs there, for more than half an hour, got to be something wrong.
August 17, 2009 at 7:38 pm
Well it tested out fine on my own data that I listed above. If you want me to test it any further I will need the DDL's and Sample data as described in this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/.
If you need help on collecting or formatting these scripts just let us know.
[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]
August 18, 2009 at 8:10 am
Hi Barry,
I already attached my data in the previous post, I am sure you can easily import the two tables into your sql server.
Thanks again for your help.
P.S. The result on your test data is actually incorrect: for example, your sample data says 'OU00000056' has a parent of 'OU00000055', which corresponding to record 11 in RSUNew table, but selecting in your view returns nothing:
select * from OrgLEvels where OrgUnitID = 'OU00000056'
August 18, 2009 at 2:10 pm
halifaxdal (8/18/2009)
I already attached my data in the previous post, I am sure you can easily import the two tables into your sql server.
This is all explained in the article, but it's actually easier for you to format it correctly as we requested (there's a tool for it) than it is for me to try and compensate on my end. Plus this is your problem, if it's not important enough for you to put in the time to help us to help you, then why should we?
[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]
August 18, 2009 at 2:54 pm
If you really want a UDF, try this:
CREATE FUNCTION HierarchyTop (
@OrgUnitID_in VARCHAR(12))
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @Top VARCHAR(12) ;
;
WITH HierarchyUp
AS (SELECT
OrgUnitID,
ISNULL(Parent, OrgUnitID) AS Parent
FROM
dbo.ISC_OrgUnits
WHERE
OrgUnitID = @OrgUnitID_in
UNION ALL
SELECT
OrgUnitID,
ISNULL(Parent, OrgUnitID) AS Parent
FROM
dbo.ISC_OrgUnits OU2
INNER JOIN HierarchyUp
ON OU2.OrgUnitID = HierarchyUp.Parent
AND OU2.Parent != HierarchyUp.Parent)
SELECT
@Top = Parent
FROM
HierarchyUp
WHERE
Parent = OrgUnitID ;
RETURN @Top ;
END ;
It's a pretty simple implementation of a recursive CTE, exactly as it's demonstrated in Books Online and on MSDN.
Be warned that using UDFs this way can result in really serious performance issues in your database.
If you regularly need to know what the top level of a hierarchy is, I recommend switching to a nested sets hierarchy. You can get the top level nearly instantly in those.
Take a look at this for more discussion on the subject: Hierarchies in SQL[/url]
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 9:38 am
This is all explained in the article, but it's actually easier for you to format it correctly as we requested (there's a tool for it)
than it is for me to try and compensate on my end. Plus this is your problem, if it's not important enough for you to put in the time to help us to help you, then why should we?
sorry Barry, I didn't know I should reformat my data and there is a tool for it. BTW, where is the tool?
August 19, 2009 at 10:28 am
Forum Etiquette: How to post data/code on a forum to get the best help
by Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
hope this helps
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 19, 2009 at 10:49 am
Thanks. The link gives error:
The specified request cannot be executed from current Application Pool
August 19, 2009 at 11:26 am
Apologies for the link:
pls try this
Forum Etiquette: How to post data/code on a forum to get the best help
by Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply