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.
Author |
Topic |
calgarychinese
Starting Member
5 Posts |
Posted - 2006-08-03 : 16:01:06
|
Hi thereI have this SQL that selects a list of employees and for each employee I need to get the leadsI 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 likeWHERE .....AND e1.EmployeeId IN (SELECT * FROM dbo.MyFunction(e1.EmployeeId)) AND .....I got the following error messagesServer: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '.'.Server: Msg 156, Level 15, State 1, Line 6Incorrect 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 lotVery 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 |
|
|
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 |
|
|
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 likeeLead.EmployeeId IN (SELECT LeadEmployeeId FROM some routine that gives me Leads for anRegularEmployee)Can it be done at all, efficiently? Thanks |
|
|
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.aspxCODO ERGO SUM |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-04 : 03:19:19
|
Maybe like this straightforward solutionINNER JOIN SomeOtherTable ON e1.EmployeeId = SomeOtherTable.EmployeeIdWHERE .....AND e1.EmployeeId IN (SELECT * FROM dbo.MyFunction(e1.EmployeeId)) AND ..... Or this variant with a derived tableINNER JOIN (select employeeid from a inner join b on a.x = b.z) so ON e1.EmployeeId = so.EmployeeIdWHERE .....AND e1.EmployeeId IN (SELECT * FROM dbo.MyFunction(e1.EmployeeId)) AND ..... Peter LarssonHelsingborg, Sweden |
|
|
calgarychinese
Starting Member
5 Posts |
Posted - 2006-08-04 : 11:33:34
|
Hi Michael Valentine JonesOK, I'll try my besttblEmployeeID OrgUnitID LeadInd=== ========= =======E1 OU1 YE2 OU2 NE3 OU3 NE4 OU3 YE5 OU4 NE6 OU4 NE7 OU7 YE8 OU3 NE9 OU9 NE10 OU3 Y tblOrgUnitID ParentID=== ========OU1 NULL OU2 OU1OU3 OU2OU4 OU3OU5 OU1OU7 OU3OU9 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 E33 C00004 E54 C00007 E55 C00005 E26 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 NULLE2 C00005 E1E3 C00001 E4E3 C00001 E10E4 NULL E1E5 C00004 E4E5 C00004 E10E5 C00007 E4E5 C00007 E10E6 NULL E4E6 NULL E10E7 NULL E4E7 NULL E10E8 NULL E4E8 NULL E10E9 C00007 E1E10 NULL E1 That's why in the query I need something likeeLead.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 aSELECT 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 2END 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 |
|
|
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 |
|
|
|
|
|
|
|