2

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!

leppie
  • 115,091
  • 17
  • 196
  • 297
ltsai
  • 757
  • 3
  • 8
  • 16
  • `CASE` in T-SQL is an **expression** - it can return literal values or SQL variables - but it **cannot** execute code (like a `SELECT` statement) to return something .... – marc_s Mar 25 '14 at 19:52

1 Answers1

5

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...)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786