SQL where clause with great than or less than in a case statement

Question

I have a list of objects with X and Y coordinate columns.

I only want to select objects with Y coordinates greater a value 5804400 when a variable value is greater than 5804400.

Here is my attempt but SQL doesn't like it.

Select * from Panels p
where case when @Val > 5804400 then p.Y > 5804400 else p.Y < 5804400 end

You can use a `case` expression in a `where` clause as explained [here](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) for an `on` clause, but it's easier to use Andy Korneyev's answer. — HABO, Jun 18 '16 at 15:40

score 5 · Accepted Answer · answered Jun 17 '16 at 13:16

5

You can't use case expression this way, but you can rewrite this like:

Select * from Panels p
where (p.Y > 5804400 and @Val > 5804400) or (p.Y < 5804400 and  @Val <= 5804400)

answered Jun 17 '16 at 13:16

Andrey Korneyev

26,353
15
70
71

score 0 · Answer 2 · answered Jun 17 '16 at 13:34

0

You can try the query below:

SELECT * 
FROM
    Panels p
WHERE
    1 =  
        CASE
            WHEN @Val > 5804400 AND p.Y > 5804400 THEN 1 
            WHEN @Val <= 5804400 AND p.Y < 5804400 THEN 1
            ELSE 0
        END

answered Jun 17 '16 at 13:34

dee.ronin

1,040
12
22

won't this just return the entire table except for the record 5804400 ? – user1666620 Jun 17 '16 at 14:46

score 0 · Answer 3 · answered Jun 17 '16 at 14:06

0

When i want to determine whether to use greater that or less than based on a condition i use this approach

Select * from Panels p
where  case when @Val > 5804400 then p.Y- 5804400 else  5804400-p.Y end>=1

answered Jun 17 '16 at 14:06

krish

881
7
11

SQL where clause with great than or less than in a case statement

3 Answers3