Syntax for CASE WHEN with multiple criteria

Posted on 2006-05-03
Last Modified: 2010-08-05
SET   Product_Type = CASE plcy.PTYP_ID WHEN 4 OR 8 OR 11 OR 15 OR 16 OR 17) then 'B' else 'A' END

What's the correct syntax for CASE WHEN {multiple values} THEN {something} END?

Question by:Jim Horn
    LVL 34

    Assisted Solution

    CASE WHEN plcy.PTYP_ID  = 4 OR plcy.PTYP_ID  = 8 OR plcy.PTYP_ID  =  11 OR plcy.PTYP_ID  = 15 OR plcy.PTYP_ID  = 16 OR plcy.PTYP_ID  = 17)
    LVL 5

    Accepted Solution

    UPDATE #tmp
    SET  Product_Type = CASE when plcy.PTYP_ID in ( 4,  8, 11,15, 16,17) then 'B' else 'A' END
    LVL 38

    Assisted Solution

    by:Jim P.
    Have you tried:
    UPDATE #tmp
    SET   Product_Type = CASE plcy.PTYP_ID WHEN 4, 8, 11,  15, 16, 17 then 'B' else 'A' END
    LVL 65

    Author Comment

    by:Jim Horn
    Thanks guys.  I was using the IN, but for some reason not able to get it to work.
    LVL 38

    Expert Comment

    by:Jim P.
    >>  16 OR 17) then 'B' els

    You had an unclosed paren in the original.
    LVL 38

    Expert Comment

    by:Jim P.
    Glad to be of assistance. May all your days get brighter and brighter.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now