SQL Server Case statement in WHERE Clause
I tried to google for CaseStatement in WHERE clause. But i didn't find similar to my scenario.
Below is my SQL Statement with CASE Statement in WHERE clause. If PartName = B, then i should apply (RecoveraleFlag = 1) condition along with other conditions. Else This condition should not apply but all other conditions should remain.
FROM Rec.Communications A INNER JOIN REC.CommunicationTypes B ON A.CommunicationTypeKey = B.CommunicationTypeKey INNER JOIN occ.Cases c ON a.CaseId = c.CaseId INNER JOIN occ.Claims cl on a.CaseId = cl.CaseId INNER JOIN ops.Concepts d ON c.ConceptKey = d.ConceptKey INNER JOIN OPS.Regions f ON d.MODSRegionKey = f.MODSRegionKey INNER JOIN COM.RepriceRequestOccurrences e ON a.CommunicationId = e.CommunicationId INNER JOIN occ.Providers prv ON c.MODSProviderKey = prv.MODSProviderKey WHERE **( CASE WHEN f.PartName = 'B' and e.RecoverableFlag = 1 then 1 ELSE 0 END ) = 1** AND b.CommunicationTypeCode = 'RREQ' AND f.Region = @Region AND a.CurrentFlag = 1
Here Case Statement in where clause is working fine if Partname = B. For Partname A, this will be 0=1 – always false. Because of this it is not returning any data. Can anyone gives any alternatives.
Appreciate your responses. Thanks
As a case statement, you would write this as:
CASE WHEN f.PartName = 'B' and e.RecoverableFlag = 1 then 1 WHEN f.ParName = 'A' then 1 ELSE 0 END ) = 1
Is this the logic you want?
Many would think that the case statement is irrelevant here, and instead use:
WHERE ((f.PartName = 'B' and e.RecoverableFlag = 1) or (f.partName <> 'B')) . . .
WHERE ( f.PartName <> 'B' OR e.RecoverableFlag = 1 ) AND b.CommunicationTypeCode = 'RREQ' AND f.Region = @Region AND a.CurrentFlag = 1
You might find reading on De Morgan's Laws interesting.