Conditional 'NOT' in a WHERE clause

• anthonyqkiernan

  SSC Rookie

  Points: 46

  More actions

  March 11, 2010 at 6:02 am

  #212369

  I'm sure there's something glaringly obvious here that I'm just not seeing. I want to do something like the following in my where clause:

  WHERE

  CASE @var

  WHEN 1 THEN x = 1

  ELSE

  NOT x = 1

  END

  Any pointers? I think I've developed some sort of boolean blindness today.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 11, 2010 at 6:04 am

  #1131966

  else x <> 1

  But...you can't CASE a part of an expression:

  WHERE x = CASE @var = 1 THEN 1 ELSE x END

  AND x <> CASE WHEN @var <> 1 THEN 1 ELSE -999 END

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• anthonyqkiernan

  SSC Rookie

  Points: 46

  More actions

  March 11, 2010 at 6:59 am

  #1131992

  Sorted. Thank you very much.