locked
Use of IN() in a CASE Statement RRS feed

  • Question

  • I like CASE statements and use them a lot, but I've run into trouble with one. I have about 100 codes on our customer record that I need to classify in a report in one of two groups. I was hoping to do somehting like this:

     [TYPE] =
    Case c.shipper  -- To consolidate into two categories
            WHEN IN ('A','B','C','D','E','F')
            THEN 'Category1'
            ELSE 'Category2'
            END

    But I always get a syntax error. Instead I have had to end up with something like this (much abbreviated here!)
     [TYPE] =
    Case c.ship_code -- To consolidate into two categories
            WHEN 'A' THEN Category1
            WHEN 'B' THEN Category1
            WHEN 'C' THEN Category1
            WHEN 'F' THEN Category1
            WHEN 'G' THEN Category1
            WHEN '1' THEN Category1
            WHEN '7' THEN Category1
            WHEN '92' THEN Category1
      
            WHEN 'Z' THEN Category1
            
            ELSE 'Category2'
            END


    I hope this is informative. This is the first time I've tried to use an "IN" list within a Case, and although I have been reading BOL all afternoon I'm thinking the explanation and examples aren't getting through today. Can this be done? Can what I've done be improved? Am I having a brain fffft?
    Thanks for any help on a Friday afternoon...
    • Moved by ashawani_dubey Monday, June 15, 2009 11:46 AM not related to bookmark (From:Social Bookmarks Discussion Forum)
    Friday, April 3, 2009 8:46 PM

Answers

All replies

  • Hello, 

    Thank you for your post!  Unfortunately I am not able to determine which forum or newsgroup is best suited for your question at hand.  Can you provide me a little more detail in regards to the product or technology you are inquiring about?

    Thanks & regards,


    Ashawani Tier 2 Application Support Server and Tools Online Engineering Live Services Team
    Monday, June 15, 2009 11:46 AM
  • T-SQL on a SQL 2000 data base.
    Monday, June 15, 2009 1:23 PM
  • Hello,

    Thank you for your post!  I would suggest creating a new thread for your question in the (SQL Server TechCenter > SQL Server Forums > Transact-SQL ) forum located here:  (http://social.technet.microsoft.com/Forums/en-US/transactsql/threads).

    Hope that would be helpful.

    Have a great day!

    Thanks & regards,


    Ashawani Tier 2 Application Support Server and Tools Online Engineering Live Services Team
    Tuesday, June 16, 2009 11:16 AM