Select statement error

[sepia]

Can anyone tell me why I'm getting the following error:

"Incorrect syntax near the keyword 'where'"

Here's the code:

Select Distinct Residence_Type = luSvc_Residence.Svc_Residence_Desc
,Begin_Date = Residence_Begin_Date
,End_Date = Residence_End_Date
,Room_Number = substring(Room_Assignment,2,3)
,Hall = substring(Room_Assignment,1,1)
from buSvc_Service_Header SH
INNER JOIN buSvc_Residence ON SH.Service_Header_ID = buSvc_Residence.Service_Header_ID
INNER JOIN buSvc_Service_Detail_Line DL ON SH.Service_Header_ID = DL.Service_Header_ID
INNER JOIN buCase_Master ON SH.Case_Master_ID = buCase_Master.Case_Master_ID
INNER JOIN buParticipant ON buCase_Master.Participant_ID = buParticipant.Participant_ID
INNER JOIN buCase_Caseload_Assign ON buCase_Master.Case_Master_ID = buCase_Caseload_Assign.Case_Master_ID
INNER JOIN buStaff_Caseload_Assign ON buCase_Caseload_Assign.Caseload_ID = buStaff_Caseload_Assign.Caseload_ID
INNER JOIN buStaff_Member SM ON buStaff_Caseload_Assign.Staff_Member_ID = SM.Staff_Member_ID
INNER JOIN buStaff_Member CM ON SH.Case_Manager_Staff_ID = CM.Staff_Member_ID
INNER JOIN buAuth_Header AH ON SH.Auth_ID = AH.Auth_ID
INNER JOIN luSvc_Residence ON buSvc_Residence.Svc_Residence_Desc = luSvc_Residence.Svc_Residence_Desc
WHERE busvc_Residence.residence_id = (select max(s2.residence_ID)
from busvc_residence s2)
where s2.service_header_ID = busvc_Residence.service_header_ID

 

[amrita418]

I did not look at your logic but the error is because you a premature closed brace for the subquery.

Move the brace to after the where clause.

Regards,
AA

 

[gmmastros]

you have 2 from's and 2 where's. This can happen when you have a subquery, but I don't see that anywhere in your query.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom