Join 136,825 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,922 people online right now. Registration is fast and FREE... Join Now!
The first part all the way to "Left Outer Join" works great! But now what I'm trying to do is take the RC.Position from the first part and use that result in the second part to get info from the correct field. Thus if RC.Position = RR, I need to pull the field RR_Assigned from the same engagement ID from the engagement table.
Hope that makes sense, below is my attempt at code which doesn't work at all (it doesn't like my case syntax).
CODE
Select C.Name, E.Classification, E.End_Quarter, E.End_Year, RC.Position, ra.Assigned From Engagements as E Inner Join Clients as C on C.ID = E.Client_ID INNER JOIN ( Select a.Client_ID as ID, a.Position as Position, a.Eng_ID as Eng_ID from Rep_Client a inner join Employees b on b.ID = a.Emp_ID where a.Emp_ID = '18' ) RC on C.ID = RC.ID and E.Engagement_ID = RC.Eng_ID LEFT OUTER JOIN ( CASE WHEN RC.Position = 'RR' THEN Select a.Client_ID as ID, a.Engagement_ID as Eng_ID, a.RR_Assigned as Assigned from Engagements a CASE WHEN RC.Position = 'WPR' THEN Select a.Client_ID as ID, a.Engagement_ID as Eng_ID, a.WPR_Assigned as Assigned from Engagements a ) ra on ra.Client_ID = E.Client_ID
Case is scalar, it returns a single value. Also, inner joins don't normally have access to outer tables. Perhaps something like this:
CODE
Select C.Name, E.Classification, E.End_Quarter, E.End_Year, RC.Position, (CASE WHEN RC.Position = 'RR' THEN E.RR_Assigned WHEN RC.Position = 'WPR' THEN E.WPR_Assigned ELSE NULL END ) as Assigned From Engagements as E Inner Join Clients as C on C.ID = E.Client_ID INNER JOIN ( Select a.Client_ID as ID, a.Position as Position, a.Eng_ID as Eng_ID from Rep_Client a inner join Employees b on b.ID = a.Emp_ID where a.Emp_ID = '18' ) RC on C.ID = RC.ID and E.Engagement_ID = RC.Eng_ID
Code works great, and I think I understand why. I'm going to try to add in the rest of the code to make that table work and see how it goes.
Question though... I'm not sure what you mean by inner joins not having access to outer tables.
My interpretation is that the below shouldn't work:
LEFT OUTER JOIN ( select a.Client_ID as ID, b.Full_Name from Rep_Client a inner join Employees b on b.ID = a.Emp_ID where a.Position = 'Lead Verifier' ) EmpL on C.ID = EmpL.ID
Since there is an inner join in an outer join. Yet this does work (you gave me that code in another post for another report I was clueless on). I don't see that I tried anything different (other than incorrect use of Case statements).
Select C.Name, E.Classification, E.End_Quarter, E.End_Year, RC.Position, (CASE WHEN RC.Position = 'RR' THEN E.RR_Assigned WHEN RC.Position = 'WPR' THEN E.WPR_Assigned WHEN RC.Position = 'PTR' THEN E.PTR_Assigned WHEN RC.Position = 'Concurring WPR' THEN E.C_WPR_Assigned ELSE NULL END ) as Assigned, (CASE WHEN RC.Position = 'RR' THEN E.RR_Completed WHEN RC.Position = 'WPR' THEN E.WPR_Completed WHEN RC.Position = 'PTR' THEN E.PTR_Completed WHEN RC.Position = 'Concurring WPR' THEN E.C_WPR_Completed ELSE NULL END ) as Completed From Engagements as E Inner Join Clients as C on C.ID = E.Client_ID INNER JOIN ( Select a.Client_ID as ID, a.Position as Position, a.Eng_ID as Eng_ID from Rep_Client a inner join Employees b on b.ID = a.Emp_ID where a.Emp_ID = '18' and a.Position <> 'Preparer' ) RC on C.ID = RC.ID and E.Engagement_ID = RC.Eng_ID
Now this part is strange. At the very bottom I'm trying to add the following: WHERE Completed is null
MS SQL says "Invalid column name 'Completed'." What the...
LEFT OUTER JOIN ( select a.Client_ID as ID, b.Full_Name from Rep_Client a inner join Employees b on b.ID = a.Emp_ID where a.Position = 'Lead Verifier' ) EmpL on C.ID = EmpL.ID
In subquery returns a result set of [ID, Full_Name] that's being passed OUT, nothing is currently going IN.
QUOTE
MS SQL says "Invalid column name 'Completed'." What the...
Case is being interpreted as the rows are process, it's doesn't yet exist for the where. No alias names exist in the where ( though I think some DBMS will do it, nonstandardly ). Long story short, two options: repeat the case for the where or make the entire query a subquery and check there.
| 