Clever Geek Handbook

Using case statement in Where section (with column)

I want to use a case statement in a where clause. But changing the where position depends on my equation.

For example:

Table

ID Column1  Column2
1    2         3 
2    4         1
3    5         4
4    4         7

Query 

select * from table
where (case when column1>=column2 then column2>3 else column1>3 end)

Expected Result

ID Column1  Column2
3    5         4
4    4         7
+3


source to share


5 answers


It sounds like you want your expression to CASE

be:



SELECT *
FROM dbo.YourTable
WHERE   CASE 
            WHEN column1 >= column2 AND column1 > 4 THEN 1
            WHEN column1 < column2 AND column2 > 4 THEN 1 
            ELSE 0 
        END = 1
+3


source


You don't need CASE

, you can use OR

:



select * from table 
where (column1 >= column2 and column1 > 3)
   or (column1 <  column2 and column2 > 3)
+1


source


How about something like

select  * 
from    table 
where   (
            case 
                when column1>=column2 
                    then column1
                else column2
            end > 4
        )
0


source


If else

can be modeled using operators Or

and And

in where

. Try it.

SELECT *
FROM   tablename
WHERE  ( column1 >= column2
         AND column1 > 4 )
        OR ( column1 < column2
             AND column2 > 4 )
0


source


Try the following:

SELECT *
FROM tableA A
WHERE (CASE WHEN A.column1 >= A.column2 THEN A.column2 
            ELSE A.column1 
       END) > 3
0


source






More articles:


All Articles