Case statement Question

• mike charity

  Newbie

  Points: 3

  More actions

  May 30, 2007 at 3:03 pm

  #372097

  I'm looking for a little help with a case stament.  I can do the basic case statement, no problem.

  But wht I'm looking for is an example of something like below using the IN word:

  select blah, blah

  from Alphabets

  WHERE Letter IN case when 'A' then ('A','E','I','O','U') else ('B','C','D','F')

   

  Is such a thing possible?

  Thanks,

  -Michael

   

   

   

• Daryl AZ

  Hall of Fame

  Points: 3087

  More actions

  May 30, 2007 at 4:11 pm

  #709840

  Not sure on performance but you avoid the case statement below...

  select blah, blah

  from Alphabets

  WHERE (yourCOlumn = 'A' AND Letter IN ('A','E','I','O','U') )

  OR (yourColumn 'A' and letter in ('B','C','D','F'))

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 4, 2007 at 8:45 am

  #710611

  Hi Mike

  Yes, you can use CASE (simple or searched) in a WHERE.

  What are you comparing to [('A','E','I','O','U') else ('B','C','D','F')]?

  Cheers

  ChrisM

  ^{“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

• mike charity

  Newbie

  Points: 3

  More actions

  June 12, 2007 at 9:03 am

  #712325

  Hi Chris,

  I'm trying to do something like this, where I'm looking for an "IN" as opposed to an "=".

  Thanks for any light you can shed,

  Mike

  declare @option int

  set @option = 1

  SELECT *

  FROM  TableAge WHERE

  Age IN case when @option = 1 then (0,15,17) else (18,21,30) end

• shrikant-279400

  SSCommitted

  Points: 1651

  More actions

  June 13, 2007 at 9:28 am

  #712658

  hey mike

  What is problem with soln suggested by Darryl? 

  declare

  @option int

  set

  @option = 1

  SELECT

  * FROM TableAge

  WHERE (age IN (0,15,17)and @option =1) or

             (age IN (18,21,30)and @option !=1)

     

   

  Regards
  Shrikant Kulkarni

• mike charity

  Newbie

  Points: 3

  More actions

  June 13, 2007 at 10:53 am

  #712680

  Nothing at all... nothing at all.

  It was just too simple for me to see  (gotta lay off the crack pipe).

  Thanks it's what I needed,

  Michael