Recursive select using UDF against a table?

Question

Recursive select using UDF against a table?

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
September 1, 2010 at 1:34 pm

#225497

I have a table of employee IDs. I have a separate function that determines all the employees reporting to a given employee ID. Is there a way I can use a CTE or some other construct to execute the function for each employee ID in the source table.
For example, table Leaders:
EmployeeID, Level, EmailAddress
function dbo.GetOrg('employee id as string') returns employeeid, email and location for every employee reporting to the employee specified in the function call.
I want to end up with a table or CTE that has all the employees for all the people in the Leaders table.
It seems like I should be able to construct this but I can't quite get there. Any help would be greatly appreciated.
:crazy:

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

rschaeferhig (9/1/2010)
I have a table of employee IDs. I have a separate function that determines all the employees reporting to a given employee ID. Is there a way I can use a CTE or some other construct to execute the function for each employee ID in the source table.
For example, table Leaders:
EmployeeID, Level, EmailAddress
function dbo.GetOrg('employee id as string') returns employeeid, email and location for every employee reporting to the employee specified in the function call.
I want to end up with a table or CTE that has all the employees for all the people in the Leaders table.
It seems like I should be able to construct this but I can't quite get there. Any help would be greatly appreciated.
:crazy:

Too bad you have a "Leaders table"... this would be pretty easy otherwise. Might still be but it depends... what does the employee table look like?

Also, if you'd like an actual tested code solution to follow, follow the suggestions of the article at the first link in my signature below. Otherwise, my only suggestion can be "if the employee table is an adjacency list, use a recursive CTE in the "lasagne" mode to expand the hierarchy to "N" Levels".

--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)

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

Thanks for the response.

The need for the Leaders table is to define who the Leaders are in the company and certain specific attributes not found in our Employee table that are used for a report.

Here's the Leader table:

CREATE TABLE [dbo].[dbo.LeaderReportingLevels](

[LeaderID] [varchar](10) NOT NULL,

[LeaderName] [varchar](255) NULL,

[BusinessUnit] [varchar](255) NULL,

[Level] [int] NULL,

[Sublevel] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

The idea is to create a table (temp or otherwise) that represents all the employees reporting to the members of the Leaders table, with all the attributes of the Leader included for each employee. (I know RBAR, but I can't envision this any other way).

The Employees table looks like:

CREATE TABLE [dbo].[ReportingHierarchy](

[EmployeeID] [varchar](10) NULL,

[higmanagerempid] [varchar](10) NULL,

[cn] [varchar](255) NULL,

[lvl] [int] NULL,

[binpath] [varbinary](max) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

I have a UDF that will take a LeaderID (EmployeeID) and return the set of employees reporting to that leader. If there's no simple way to create that set using the UDF (select * from dbo.GetOrg([Leaderid])) then here's the select from the UDF that retrieves a manager's (leader's) organization:

select s.employeeid, s.cn

from reportinghierarchy r join

reportinghierarchy s

on cast(s.binpath as varchar(max)) like cast(r.binpath as varchar(max))+'%'

where r.employeeid=@id

This is SQL 2005 so I don't have hierarchical data types. We can't use SQL 2008. This is a work-around presented at Tech Ed 2009 that emulates the hierarchical data type functionality in SQL 2005.

(EDIT)

Forgot to include some data:

Leaders:

I'm not sure how to give you data for the ReportingHierarchy table since one of the fields is varbinary. Your example doesn't work even if I cast it to varchar. So here is some raw data for the first leader:

Hope this helps...

If you can provide a working piece of code that at least gets me in the ballpark it would help greatly.

Thanks.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

Please post a use case and an example of the expected output.

Todd Fifield

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

rschaeferhig (9/2/2010)
Thanks for the response.
The need for the Leaders table is to define who the Leaders are in the company and certain specific attributes not found in our Employee table that are used for a report.
Here's the Leader table: ... etc, etc, etc...

Thanks for taking the time to do that. I'll see what I can do. The binary thing isn't a problem either... If it works out to be what I believe it is, it can be quite helpful for other things.

At this point, I don't believe I need a use-case but an example of how you'd like to see the output might be helpful.

--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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Ok... they pretty much screwed you by using the "Hierarchical Path" method of building hierarchies. There's a much more effecient method called "Nested Set Hierarchies" and if you do a search for that term, you'll find a bunch of articles on Nested Sets and how to use them, etc. Since I may be presenting a session at PASS on those subjects, all I can say is do the search and include "Ben-Gan or Celko" in your search criteria.

In the meantime, here's a function to traverse the "Hierarchical Path" for a given employee. You'll immediately see that an INDEX SEEK will not be possible with the query and THAT's one of the problems with the "Hierarchical Path" method. It's also why they had to write CLR's for the new HierarchyID methods in 2008... they're relatively slow when compared to Nested Sets. Heh... of course, Nested Sets has it's own problems like any type of hierachical structure does.

Here's the function and it's followed by one example usage for the data you gave in this post... notice the binpath is nothing more than concatenated "uplines"...

CREATE FUNCTION dbo.GetEmployeeDownLine

(@EmployeeID INT)

RETURNS TABLE AS

RETURN

WITH cteBinPath AS

( --=== Find the BinPath for the given employee

SELECT BinPathLength = LEN(BinPath),

BinPath

FROM dbo.ReportingHierarchy

WHERE EmployeeID = @EmployeeID

) --=== Find all employees with that same pedigree

SELECT h.EmployeeID, h.higmanagerempid, h.cn, h.lvl, h.binpath

FROM dbo.ReportingHierarchy h

INNER JOIN cteBinPath p

ON LEFT(h.binpath,p.BinPathLength) = p.BinPath

;

GO

--===== Example usage

SELECT * FROM dbo.GetEmployeeDownLine(241507)

--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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

... and let me know if that's what you wanted. I couldn't do much more because of the missing data in your tables.

--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)

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 7

Jeff,

Thanks for all your work on this.

I already have a function similar to your dbo.GetEmployeeDownLine(ID) function. My end use case is that I need a table of all the employees returned by dbo.GetEmployeeDownLine(ID) plus the fields in the Leaders table. Almost a JOIN of the Leaders table to the Reporting Hierarchy table.

Maybe that's the answer? If I get the BinPath for each leader, then join Leaders to Reporting Hierarchy using Leaders.BinPath LIKE ReportingHierarchy.BinPath+'%'? I know it's not optimal but it's better than cursoring through the Leaders table and running SELECT * FROM dbo.GetEmployeeDownLine(ID) for each Leader ID.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

rschaeferhig (9/7/2010)
Jeff,
Thanks for all your work on this.
I already have a function similar to your dbo.GetEmployeeDownLine(ID) function. My end use case is that I need a table of all the employees returned by dbo.GetEmployeeDownLine(ID) plus the fields in the Leaders table. Almost a JOIN of the Leaders table to the Reporting Hierarchy table.
Maybe that's the answer? If I get the BinPath for each leader, then join Leaders to Reporting Hierarchy using Leaders.BinPath LIKE ReportingHierarchy.BinPath+'%'? I know it's not optimal but it's better than cursoring through the Leaders table and running SELECT * FROM dbo.GetEmployeeDownLine(ID) for each Leader ID.

It's easy using a CROSS APPLY. The key is that the employee table and the leader table have to have something in common. Is that something in common the higmanagerempid column in the employee table???

--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)

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 9

There's no direct correlation between the tables. The only thing is the BinPath. I could join Leaders to ReportingHierarchy based on the "LEFT(r.BinPath,LEN(r.BinPath)) = l.BinPath" understanding that using LEN in a JOIN isn't optimal. That might give me all the people reporting to all the leaders. I can give it a try...

I could then join the result of that to Employees based on EmployeeID and get all the fields I need.