Arbaaz Arbaaz - 5 months ago 10x
SQL Question

SQL: Greater than or equal to in where clause based on case condition?

I want to select such that if

then I would want to select rows where level is equal to 1, otherwise where level is greater than 2.

select a, b, c, level
from table1
where col1 ='x' and
CASE @reporteetype WHEN 'immediate' THEN level = '2'
WHEN 'other' THEN level > 2
else 1 = 1

This is a simplified version of my scenario. I do not want to use dynamic query. I want to know if this could be done using case?


You can do it using combinations of AND and OR conditions:

    a, b, c, level
FROM table1
    col1 = 'x'
    AND (
        (@reporteetype = 'immediate' AND level = 2)
        OR (@reporteetype = 'other' AND level > 2)