CODEWITHSUNDEEP
sqlsql-servercasewhere-clause
ltsai
ltsai

Reputation: 757

Is it possible to have a CASE WHEN THEN SELECT in a WHERE CLAUSE

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!

Upvotes: 2

Views: 25289

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Yes, this is possible under the following circumstances:

  1. The subqueries are returning one value.
  2. 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...)

Upvotes: 5

Related Questions