CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008
Nimmi
Nimmi

Reputation: 690

Issue while using 'case when ' in 'where' clause sql server

While using case when in where clause in sql query it's not working.

Problem :

I have two tables named TblEmployee and TblAssociate.Both tables contains common columns PeriodId, EmpId and AssociateId. My requirement is to fetch values from TblEmployee with combination of EmpId and AssociateId from TblAssociate should be excluded.And the exclusion should be based on PeriodId condition.`

If(@PeriodID<50)
BEGIN
  SELECT * 
  FROM TblEmployee 
  WHERE (EmpId+AssociateId) NOT IN (SELECT EmpId+AssociateId FROM TblAssociate)
END
ELSE
BEGIN
  SELECT * 
  FROM TblEmployee 
  WHERE (EmpId) NOT IN (SELECT EmpId FROM TblAssociate)
END

The above code is working, but I need to avoid that IF-ELSE condition and I wish to use 'case when' in where clause.Please help

Upvotes: 2

Views: 133

Answers (3)

Lajos Arpad
Lajos Arpad

Reputation: 76814

The addition of IDs do not guarantee uniqueness. For instance, if EmpId is 5 and AssociateId is 6, then EmpId + AssociateId = 11, while EmpId + AssociateId = 11 even if EmpId is 6 and AssociateId is 5. In the query below, I made sure that the subquery will stop searching when the first record is found and will return a single record, having the value of 1. We select the employee if and only if 1 is among the results. In the subquery we check the operand we are sure of first and then check if we are not in a period where AssociateId must be checked, or it matches.

select *
from TblEmployee
where 1 in (select top 1 1
            from TblAssociate
            where TblEmployee.EmpId = TblAssociate.EmpId and
            (@PeriodID >= 50 or TblEmployee.AssociateId = TblAssociate.AssociateId))

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You could use AND-OR combination in the WHERE clause. Additionally, you should not be using + as it may lead to incorrect result. You can rewrite your query as:

SELECT e.*
FROM TblEmployee e
WHERE
    (
        @PeriodID < 50 
        AND NOT EXISTS(
            SELECT 1 
            FROM TblAssociate a 
            WHERE 
                a.EmpId = e.EmpId
                AND a.AssociateId = e.AssociateId
        )
    )
    OR 
    (
        @PeriodID >= 50 
        AND NOT EXISTS(
            SELECT 1 
            FROM TblAssociate a 
            WHERE a.EmpId = e.EmpId
        )
    )

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Try this:

SELECT * 
FROM TblEmployee 
WHERE (EmpId + CASE WHEN @PeriodID<50 THEN AssociateId ELSE 0 END) NOT IN 
      (SELECT EmpId + CASE WHEN @PeriodID<50 THEN AssociateId ELSE 0 END FROM TblAssociate)

You say your code is working but this is rather odd, since it doesn't make much sense to add together id values. In any case, the above statement produces a result that is equivalent to the code originally posted.

Upvotes: 2

Related Questions