CODEWITHSUNDEEP
sqlsql-server
user6116267
user6116267

Reputation:

Case when then in where clause

How to use case when then in where clause if one of the conditions is fulfilled?What I need is if there is only one condition to be executed after the first condition is met If a condition meets the following conditions, it will not be executed.The case statement will stop evaluating further as soon as the first when condition is met

SELECT TOP 100 ProjType, projCost, InvestAmount, ProjBuildSize
FROM dbo.Project 
WHERE
   (
     CASE
     WHEN projCost > 0  then projCost end >= 10000000 
     WHEN InvestAmount IS NOT NULL then InvestAmount end >= 12000000
     WHEN ProjBuildSize > 0 then ProjBuildSize ELSE >= 5000
     END
   )

Upvotes: 1

Views: 102

Answers (3)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14218

You can achieve it in this simple way

SELECT TOP 100 ProjType,projCost,InvestAmount,ProjBuildSize FROM dbo.Project 
WHERE (projCost <= 0 OR projCost >= 10000000)
      AND (projCost <= 0 And ((InvestAmount is null)  OR InvestAmount >= 12000000))
      AND ((InvestAmount is null And projCost <= 0) And ((ProjBuildSize <= 0 ) OR ProjBuildSize  >= 5000))

Upvotes: 0

rohitvats
rohitvats

Reputation: 1851

You can use the case statement like this in your where clause:

SELECT TOP 100 ProjType, projCost, InvestAmount, ProjBuildSize
FROM dbo.Project 
WHERE
   (
     CASE
     WHEN projCost > 0 AND projCost >= 10000000 then 1  
     WHEN InvestAmount IS NOT NULL AND InvestAmount >= 12000000 then 1
     WHEN ProjBuildSize > 0 AND ProjBuildSize >= 5000 then 1
     ELSE 0
     END
   ) = 1

If any of your when conditions are satisfied, the case will return a 1, and the where clause condition will be satisfied, otherwise the case will return 0.

Upvotes: 1

Yusef
Yusef

Reputation: 306

You don't need CASE WHEN here, a simple x OR y OR z will suffice.

Also, if InvestAmount is a nullable column, then to check for null you must do InvestAmount IS NOT NULL instead of InvestAmount <> NULL

SELECT TOP 100 ProjType,projCost,InvestAmount,ProjBuildSize 
FROM dbo.Project 
WHERE
    (projCost > 0 AND projCost >= 10000000)
    OR (InvestAmount IS NOT NULL AND InvestAmount >= 12000000)
    OR (WHEN ProjBuildSize > 0 AND ProjBuildSize  >= 5000)

Upvotes: 2

Related Questions