-4

I have a query that looks like this:

SELECT col1,col2
FROM dbo.table1
WHERE
(CASE WHEN col1 > 5 THEN col2) > 10
OR
(CASE WHEN col1 <= 5 THEN col2) > 20

I am trying to achieve a result set that will give me the following information: In case that col1>5 then retrieve all the values in col2 that are larger then 10 OR In case that col1 <= 5 then retrieve all the values in col2 that are larger then 20 , my question is: is the method above correct for filtering out that result set?

masteusz
  • 3,461
  • 3
  • 13
  • 17
Aby_g
  • 53
  • 1
  • 7
  • Yes, provided you explain what you want What does `then display only col2>10 ` mean? Display rows only if `col2` is greater than 10? You don't need `CASE` for that – Panagiotis Kanavos Dec 19 '21 at 19:28
  • you can simply put your requirement in `where` clause - like `WHERE (col1>5 AND col2>10) OR (col1<=5 AND col2>20` – an33sh Dec 19 '21 at 19:30
  • 1
    Your question needs to be phrased clearer, you don't normally use a *case expression* to filter rows, and what does `then display only col2>10` mean? – Stu Dec 19 '21 at 19:37
  • 1
    `display only col2 > 10` what does that mean? Display TRUE? Display one or the other column only if COL2 > 10? T-SQL has no boolean type so you can't use a boolean expression as a value. If you want to return something that can be translated to TRUE/FALSE you need to return 1 or 0 explicitly. – Panagiotis Kanavos Dec 19 '21 at 19:42
  • [This](https://stackoverflow.com/a/10260297/92546) answer explains how to use a `case` expression in a `join` condition. It also applies to a `where` clause. Expect performance to range from dreary to abysmal. – HABO Dec 20 '21 at 04:23

1 Answers1

2

If you want to display only rows where col2 is greater than 10 or 20 you don't need CASE. You can use OR

select col1, col2
from table1
where (col1>5 and col2>10) OR (col1<=5 and col2>20)

If on the other hand you want to display whether COL2 is above or below another number, you need a CASE in the SELECT clause, not WHERE.

SELECT col1, col2, 
    CASE WHEN COL1 > 5 THEN IIF(COL2>10,1,0)
         WHEN COL1 <=5 THEN IIF(COL2>20,1,0)
    END
FROM table1

T-SQL has no boolean type so a boolean expression can't be used as a value. If you want to return something that can be translated to TRUE or FALSE by the client application you need to return it explicitly.

IIF is a shortcut for CASE WHEN condition THEN a ELSE b END.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • This doesnt answer the question, the meaning is, if col1>5 then display col2>10, the col2>10 is not a condition – Aby_g Dec 19 '21 at 19:33
  • @Aby_g again, what does this even mean? Do you want to display `TRUE` or `FALSE`? You can't just display `col2>10` in SQL - especially in T-SQL which has no boolean – Panagiotis Kanavos Dec 19 '21 at 19:36
  • 1
    @Aby_g I added a different query that may answer your question. You really need to explain what you want though – Panagiotis Kanavos Dec 19 '21 at 19:44