CODEWITHSUNDEEP
sqlsql-server
proseidon
proseidon

Reputation: 2305

Understanding case expression in the "Where" clause

I've got this code here and you can see from my Pseudocode what I'm trying to accomplish

select *

from dbo.BenefitsForms
     inner join Dependents on BenefitsForms.UserId = Dependents.BenefitsForm_UserId
     inner join CoverageLevels on BenefitsForms.MedicalId = CoverageLevels.Id

where (BenefitsForms.MedicalId > 0 AND BenefitsForms.MedicalId < 13)
  AND Dependents.IsSpouse = CASE when CoverageLevels.[Level] = 2 then 1
                                 when CoverageLevels.[Level] = 3 then 0 end
                                 when CoverageLevels.[Level] = 4 then [any, it doesnt matter] <--- my desire but it doesn't work.

What can I do to get the effect I desire in the brackets? If Coverage Level = 4 then I don't care what Dependents.IsSpouse is, I don't even need to sort by it anymore.

Upvotes: 2

Views: 67

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Assuming that isSpouse can only be 0 or 1... if CoverageLevels.Level is 4, then compare isSpouse to itself, which will always result in true:

AND Dependents.IsSpouse = CASE 
    when CoverageLevels.[Level] = 2 then 1
    when CoverageLevels.[Level] = 3 then 0
    when CoverageLevels.[Level] = 4 then Dependents.IsSpouse
END

Alternately, this can also be expressed without the CASE:

WHERE
    BenefitsForms.MedicalId > 0 
    AND BenefitsForms.MedicalId < 13
    AND (
        (Dependents.IsSpouse = 1 AND CoverageLevels.[Level] = 2)
        OR (Dependents.IsSpouse = 0 AND CoverageLevels.[Level] = 3)
        OR CoverageLevels.[Level] = 4
    )

Upvotes: 7

Related Questions