adding a where condition for one criteria in sql query

Question

I need to add a where condition for a SQL query , means when the dept id become 9 only i need that where condition , else i dont required.

i have written the below query , is the approach is correct?

SELECT 
b.DeptId,
b.DeptName,
a.SurveyID,
a.SurveyName,
a.Status,
a.AllUsers, 
IsNull(a.SelectedUsers,'') as SelectedUsers,
a.OpenDate,
a.CloseDate,
e.Role as RoleName 
from Surveys a 
inner join Departments b 
on a.deptid=b.deptid 
left outer join
 surveyUsers c 
on c.surveyid=a.SurveyID 
and c.empCode= 9902
left outer join [360HRSurveyEmployee] d 
on d.surveyid=a.SurveyID 
left outer join [360HRSurvey] e 
on e.sempid = c.empCode 
and e.empid = d.empid 
where ( c.empCode= 9902 or a.AllUsers = 1 ) 
and a.status in (1) 
and a.OpenDate <= '6/9/2015' 
and a.CloseDate >= '6/9/2015'
and CASE WHEN DeptId == 9 
         THEN e.Role IS NOT NULL END
 order by b.DeptID,a.SurveyID 

Note the last three lines in the above query where i added the case :

and CASE WHEN DeptId == 9 
         THEN e.Role IS NOT NULL END
 order by b.DeptID,a.SurveyID 

I am getting a syntax error also

Incorrect syntax near '='.

Answer 1

If I understand you correctly, you only need rows where DeptId is not 9, or DeptId is not null. Also, your gross disregard for consistency in your capitalization hurts me. What is this beast?!

SELECT
    b.DeptID, b.DeptName,
    a.SurveyID, a.SurveyName, a.Status, a.AllUsers,
    ISNULL(a.SelectedUsers,'') as SelectedUsers,
    a.OpenDate, a.CloseDate, e.Role as RoleName
FROM
    Surveys AS a
    INNER JOIN Departments AS b
        ON a.DeptID = b.DeptID
    LEFT OUTER JOIN SurveyUsers AS c
        ON (c.SurveyID = a.SurveyID AND c.EmpCode = 9902)
    LEFT OUTER JOIN [360HRSurveyEmployee] AS d
        ON d.SurveyID = a.SurveyID
    LEFT OUTER JOIN [360HRSurvey] AS e
        ON (e.EmpID = c.EmpCode AND e.EmpID = d.EmpID)
WHERE
    (
        c.EmpCode = 9902
        OR a.AllUsers = 1
    )
    AND a.Status = 1
    AND a.OpenDate <= '6/9/2015'
    AND a.CloseDate >= '6/9/2015'
    AND (
        a.DeptID != 9
        OR e.Role IS NOT NULL
    )
ORDER BY
    a.DeptID,
    a.SurveyID;

Answer 2

and CASE WHEN DeptId == 9

You only need one = sign for this comparison.

Answer 3

You need to use single =

CASE WHEN DeptId = 9 
     THEN e.Role END

What exactly do you want to achive with this line? THEN e.Role IS NOT NULL END