Is it possible to have a CASE WHEN THEN SELECT in a WHERE CLAUSE
case, sql, sql-server, where-clause
Solution
Yes, this is possible under the following circumstances:
- The subqueries are returning one value.
- There is an outside comparison such as `=` or `>`
The `case` statement returns scalar values. A row with one column and one value is "equivalent" to a scalar value. So, the following would be allowed:
where col = (CASE WHEN date > '2014-02-28' THEN (SELECT max(col2) FROM TABLEC WHERE...)
ELSE (SELECT min(col3) FROM TABLE B WHERE...)
END)
But, you probably want to do a conditional `in` statement. Eschew the `case`:
where date > '2014-02-28' and col in (SELECT max(col2) FROM TABLEC WHERE...) or
date <= '2014-02-28' and col in (SELECT min(col3) FROM TABLE B WHERE...)
Problem
I was just wondering if it would be possible to have a CASE statement in a WHERE clause exactly in this form... ``` SELECT * FROM TABLEA WHERE date between '2014-02-01' and '2014-02-28' and CASE WHEN date>'2014-02-28' THEN (SELECT FROM TABLEC WHERE...) ELSE (SELECT FROM TABLE B WHERE...) END ``` Thanks!