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

Answers


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.


Need Your Help

SQL Query Source Code

sql sql-server database sql-server-2008

I have 3 tables. One table has all the people, [Pat], each with a unique [PatId]. The second table has all the insurance company information, [Ins], each with a unique [InsId]. The third table has ...

jQuery hover event firing twice on mouse over

jquery hover

I'm trying to temporarily change the contents of a div on hover, using this jQuery:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.