Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SQL Server user defined function problem

Author  Topic 

calgarychinese
Starting Member

5 Posts

Posted - 2006-08-03 : 16:01:06
Hi there

I have this SQL that selects a list of employees and for each employee I need to get the leads

I have this user function that, passed in an employee id, will pass out a single-column table of lead employee ids.

In my big SQL statement then I have something like

WHERE .....
AND e1.EmployeeId IN (SELECT * FROM dbo.MyFunction(e1.EmployeeId))
AND .....

I got the following error messages
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'.
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.

Is it complaining because my parameter is e1.EmployeeId? The thing is I have 2 instances of employee table so I have e1 and e2, and therefore I need to qualify the table instance.

I tried (just for the heck of it) removing e1. and I got this:

Server: Msg 155, Level 15, State 1, Line 1
'EmployeeId' is not a recognized OPTIMIZER LOCK HINTS option.

How will I be able to successfully use user-defined function in a where clause?


Thanks a lot
Very miserable now.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-03 : 16:11:44
You cannot use a table value function where the input parameter is from a query result.




CODO ERGO SUM
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-03 : 16:21:06
quote:

WHERE .....
AND e1.EmployeeId IN (SELECT * FROM dbo.MyFunction(e1.EmployeeId))



If you could do that, it would also be very inefficient. It is much better to express that condition as a JOIN and to remove the UDF if possible.


- Jeff
Go to Top of Page

calgarychinese
Starting Member

5 Posts

Posted - 2006-08-03 : 19:03:00
Hi there, thanks for the quick reply.

I'm trying to gather my thoughts so I can explain better the situation.

The query that I use, amongst others, contains 2 instances of the employee table because one instance I need to get the employee name, the other instance I need to get the name of the lead of the corresponding employee.

The relationship between the employee and his lead is not too trivial.

Each employee belongs to an organization unit and has an indicator whether the employee is a lead or not. So let's say in the employee table there are columns called Id, OrgUnitId and LeadInd.

If an employee is not a lead, then employee(s) in the same OrgUnitId with LeadInd = YES is/are the lead.

If the emplyee is already a lead, then I need to go up the hierarchy chain to find the next parent OrgUnit that has a Lead, and then Lead there will be the Lead of this employee. (So one OrgUnit can have at most one parent OrgUnit)

If the employee is not a lead, and there are no leads in the same OrgUnit, then I have to go up the hierharcy chain to find the next parent OrgUnit with a Lead.

So as an example, I'm Emp1 and I'm in OU3. I'm not a Lead. There are no other leads in OU3. OU3 is a sub-unit of OU2, but OU2 also doesn't have a Lead. OU2 is a sub-unit of OU1 and OU1 has a lead. That lead will be the lead for Emp1.

The rules are complicated but they are summarized like the above. That's why it's not that easy to just use a JOIN to join them appropriately.

I would LOVE to not use UDF because we've not used UDF before - but i don't know how I can solve this problem. I can create temp tables, but it'll slow down the query also. If I have 250 employees (which I do now in my test environment) the query I write takes 40 seconds. Can't imagine what it would be like for clients with 1000 employees.

So bascially, I just want to do something like

eLead.EmployeeId IN (SELECT LeadEmployeeId FROM some routine that gives me Leads for anRegularEmployee)

Can it be done at all, efficiently?

Thanks
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-03 : 22:51:23
quote:
Originally posted by calgarychinese
...Can it be done at all, efficiently?...

Given that you haven’t shown us your table structures, any sample data, or even your query, any answer would just be a wild guess.

Take a look at the directions in this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx







CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-04 : 03:19:19
Maybe like this straightforward solution
INNER JOIN   SomeOtherTable ON e1.EmployeeId = SomeOtherTable.EmployeeId
WHERE .....
AND e1.EmployeeId IN (SELECT * FROM dbo.MyFunction(e1.EmployeeId))
AND .....
Or this variant with a derived table
INNER JOIN   (select employeeid from a inner join b on a.x = b.z) so ON e1.EmployeeId = so.EmployeeId
WHERE .....
AND e1.EmployeeId IN (SELECT * FROM dbo.MyFunction(e1.EmployeeId))
AND .....

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

calgarychinese
Starting Member

5 Posts

Posted - 2006-08-04 : 11:33:34
Hi Michael Valentine Jones

OK, I'll try my best

tblEmployee
ID   OrgUnitID  LeadInd
=== ========= =======
E1 OU1 Y
E2 OU2 N
E3 OU3 N
E4 OU3 Y
E5 OU4 N
E6 OU4 N
E7 OU7 Y
E8 OU3 N
E9 OU9 N
E10 OU3 Y


tblOrgUnit
ID   ParentID
=== ========
OU1 NULL
OU2 OU1
OU3 OU2
OU4 OU3
OU5 OU1
OU7 OU3
OU9 OU1


Now, for each employee I need to display some employee information, such as first name, last name, and each training course he took (via tblEmpTraining to tblTraining to get the information about the courses themselves). And I also need to display the employee's lead and lead's info. Note a person can have multiple leads as in the case of OU3 (where E4 and E10 are both Leads).

tblEmpTraining (NOTE Id is just the primary key)
Id   CourseID  EmployeeID  
=== ======== ==========
1 C00001 E9
2 C00001 E3
3 C00004 E5
4 C00007 E5
5 C00005 E2
6 C00007 E9


As I mentioned before, if an employee in an Org Unit is not a lead, then the lead is someone in the same Org Unit where LeadInd = "Y"

If no such person is found, then have to go up the chain according to tblOrgUnit until you have someone with LeadInd = "Y".

E1 is a special case because OU1 is the highest level so he will always be Lead.

E2 belongs to OU2 with no lead, but OU1 is OU2's parent, so OU1's lead became OU2's lead.

E3 belongs to OU3 with 2 leads. For each course E3 takes, 2 rows were spit out, one for each lead. Similar for E5 with 2 courses (so 4 rows altogether - two for each lead), and even E6, E7, E8 with no courses, one row for each Lead with a NULL under the CourseId.

Based on the above I need to display:

EmployeeID  CourseID  LeadEmployeeId
========== ======== ==============
E1 NULL NULL
E2 C00005 E1
E3 C00001 E4
E3 C00001 E10
E4 NULL E1
E5 C00004 E4
E5 C00004 E10
E5 C00007 E4
E5 C00007 E10
E6 NULL E4
E6 NULL E10
E7 NULL E4
E7 NULL E10
E8 NULL E4
E8 NULL E10
E9 C00007 E1
E10 NULL E1


That's why in the query I need something like
eLead.EmployeeId IN (SELECT * FROM dbo.GetLeadFunctions(eReg.EmployeeId))

where eLead is the table alias of tblEmployees for the Lead instance, and eReg is the table alias of tblEmployees for the Reg instance.

The whole point is that, without this "GetLeadFunctions" function, it is not trivial how it can be done and I cannot simply write just one SELECT to do that (or maybe simply due to my incompetence as I'm fairly new to SQL Server). It's almost like I need a

SELECT CASE <is there a lead in the same org unit?>
THEN
RUN SELECT-STATEMENT 1 (i.e. find someone with LeadInd='Y' in the same unit)
ELSE
RUN SELECT-STATEMENT 2
END


But SELECT-STATEMENT 2 is not that straightforward either. I have to go up the hierharcy chain to find some unit with a Lead, and that is recursive in nature.

So without a function I don't know how to do.

DOes that give enough information?

Thanks
Go to Top of Page

calgarychinese
Starting Member

5 Posts

Posted - 2006-08-04 : 11:53:40
Don't know if that is interesting to you or not, right now I actually have a big cursor that does the above for each employee. So for 250 employees I have to explicitly call a stored proc 250 times that finds a set of Leads for a given employee ID. THat is proven to be quite slow, and so that's why I want to remove the cursor, but to do that I thought I need some UDF. If I don't need a UDF and still achieve what I want to do, I would be ectastic.

Thanks
Go to Top of Page
   

- Advertisement -