How do I do multiple CASE WHEN conditions using SQL Server 2008?

Question

What I'm trying to do is use more than one CASE WHEN condition for the same column.

Here is my code for the query:

   SELECT   Url='',
            p.ArtNo,
            p.[Description],
            p.Specification,
            CASE 
            WHEN 1 = 1 or 1 = 1 
               THEN 1 
               ELSE 0 
            END as Qty,
            p.NetPrice,
            [Status] = 0
      FROM  Product p (NOLOCK)

However, what I want to do is use more then one WHEN for the same column "qty".

As in the following code:

IF
// CODE
ELSE IF
// CODE
ELSE IF
// CODE
ELSE
// CODE

Is there something about case when <condition> then <vaue> when <condition> then <value> . . . end that isn't working? — Gordon Linoff
– Gordon Linoff, Commented Jan 31, 2013 at 16:53
As @GordonLinoff was saying, you can have more than one WHEN. — Kermit
– Kermit, Commented Jan 31, 2013 at 16:54
It's exactly as you say. I had put a parenthesis in the wrong place. Sorry guys! — Nils Anders
– Nils Anders, Commented Jan 31, 2013 at 16:58

Le Magicien Annonyme · Accepted Answer · 2022-07-06 14:48:42Z

579

There are three formats of case expression. You can do CASE with many WHEN as;

CASE  WHEN Col1 = 1 OR Col3 = 1  THEN 1 
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

Or a Simple CASE expression

CASE Col1 WHEN 1 THEN 11 WHEN 2 THEN 21 ELSE 13 END

Or CASE within CASE as;

CASE  WHEN Col1 < 2 THEN  
                    CASE Col2 WHEN 'X' THEN 10 ELSE 11 END
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

edited Jul 6, 2022 at 14:48

Le Magicien Annonyme

617 bronze badges

answered Jan 31, 2013 at 16:58

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

d8aninja Over a year ago

THERE ARE TWO. Lists three :D

Luis Deras Over a year ago

Is the inner case being validated if the outter case doesn't return true?

natewelch_ Over a year ago

@d8aninja They only listed 2. The third is a combination of the first two, with the outer case being the first type and the inner case being the second type.

daniel aagentah · Accepted Answer · 2016-06-29 15:31:40Z

25

Just use this one, You have to use more when they are classes.

SELECT   Url='',
         p.ArtNo,
         p.[Description],
         p.Specification,
         CASE 
         WHEN 1 = 1 or 1 = 1 
            THEN 1 
         WHEN 2 = 2
             THEN 2
         WHEN 3 = 3
              THEN 3
          ELSE 0 
        END as Qty,
        p.NetPrice,
        [Status] = 0
  FROM  Product p (NOLOCK)

edited Jun 29, 2016 at 15:31

daniel aagentah

1,6925 gold badges36 silver badges57 bronze badges

answered Jan 31, 2013 at 16:57

Shankar

87610 silver badges24 bronze badges

Comments

Abhijeet Navgire · Accepted Answer · 2018-03-23 12:25:09Z

13

You can use below example of case when with multiple conditions.

SELECT
  id,stud_name,
  CASE
    WHEN marks <= 40 THEN 'Bad'
    WHEN (marks >= 40 AND
      marks <= 100) THEN 'good'
    ELSE 'best'
  END AS Grade
FROM Result

answered Mar 23, 2018 at 12:25

Abhijeet Navgire

7638 silver badges21 bronze badges

Comments

Ricardo Roa · Accepted Answer · 2020-01-10 23:46:44Z

Something Like this, Two Conditions Two Columns

SELECT ITEMSREQ.ITEM AS ITEM,
       ITEMSREQ.CANTIDAD AS CANTIDAD,
       (CASE  WHEN ITEMSREQ.ITEMAPROBADO=1 THEN 'APROBADO'
              WHEN ITEMSREQ.ITEMAPROBADO=0 THEN 'NO APROBADO'
        END) AS ITEMS,
        (CASE 
              WHEN ITEMSREQ.ITEMAPROBADO = 0 
              THEN CASE WHEN REQUISICIONES.RECIBIDA IS NULL  THEN 'ITEM NO APROBADO PARA ENTREGA' END
              WHEN ITEMSREQ.ITEMAPROBADO = 1 
              THEN CASE WHEN REQUISICIONES.RECIBIDA IS NULL THEN 'ITEM AUN NO RECIBIDO' 
                        WHEN REQUISICIONES.RECIBIDA=1 THEN 'RECIBIDO' 
                        WHEN REQUISICIONES.RECIBIDA=0 THEN 'NO RECIBIDO' 
                       END
              END)
              AS RECIBIDA
 FROM ITEMSREQ
      INNER JOIN REQUISICIONES ON
      ITEMSREQ.CNSREQ = REQUISICIONES.CNSREQ

Daz · Accepted Answer · 2017-09-22 01:15:03Z

4

This can be an efficient way of performing different tests on a single statement

select
case colour_txt 
  when 'red' then 5 
  when 'green' then 4 
  when 'orange' then 3
else 0 
end as Pass_Flag

this only works on equality comparisons!

answered Sep 22, 2017 at 1:15

Daz

413 bronze badges

Comments

Jimoc · Accepted Answer · 2013-01-31 16:57:40Z

2

    case when first_condition
      then first_condition_result_true
    else
      case when second_condition 
        then second_condition_result_true
      else
          second_condition_result_false                              
      end
    end
  end as qty

answered Jan 31, 2013 at 16:57

Jimoc

1,5101 gold badge10 silver badges20 bronze badges

2 Comments

Barmar Over a year ago

There's no need to nest CASES expressions like this, you can have multiple WHEN clauses in a single CASE.

Sun Over a year ago

When the first condition is met, the rest of the conditions are ignored?

Barranka · Accepted Answer · 2015-03-17 17:17:35Z

2

case 
    when a.REASONID in ('02','03','04','05','06') then
        case b.CALSOC 
            when '1' then 'yes' 
            when '2' then 'no' 
            else 'no' 
        end
    else 'no' 
end

edited Mar 17, 2015 at 17:17

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

answered Mar 17, 2015 at 13:36

wennykikkok

211 bronze badge

1 Comment

tmt Over a year ago

Please, format your answer and possibly add some explanation.

Le Magicien Annonyme · Accepted Answer · 2022-07-11 09:30:10Z

2

Its just that you need multiple WHEN for a single case to behave it like if.. Elseif else..

       CASE WHEN 1=1       --if
       THEN
        WHEN 1=1              --else if
         THEN ... 
        WHEN ...             --else if
        THEN 
        ELSE                      --else
       ......
         END

edited Jul 11, 2022 at 9:30

Le Magicien Annonyme

617 bronze badges

answered Jan 11, 2020 at 0:28

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Comments

Durlove Roy · Accepted Answer · 2022-04-20 04:38:11Z

You will get an idea from this code.

SELECT

(CASE
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1001' THEN 'DM'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1002' THEN 'GS'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1003' THEN 'MB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1004' THEN 'MP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1005' THEN 'PL'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1008' THEN 'DM-27'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1011' THEN 'PB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1012' THEN 'UT-2'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1013' THEN 'JGC'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1014' THEN 'SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1015' THEN 'IR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1016' THEN 'UT-3'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1017' THEN 'UT-4'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1019' THEN 'KR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1020' THEN 'SYB-SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1021' THEN 'GR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1022' THEN 'SYB-KP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1026' THEN 'BNS'

  ELSE ''
END) AS OUTLET

FROM matrixcrm.Transact

Amicable · Accepted Answer · 2014-01-09 15:13:54Z

0

I had a similar but it was dealing with dates. Query to show all items for the last month, works great without conditions until Jan. In order for it work correctly, needed to add a year and month variable

declare @yr int
declare @mth int

set @yr=(select case when month(getdate())=1 then YEAR(getdate())-1 else YEAR(getdate())end)
set @mth=(select case when month(getdate())=1 then month(getdate())+11 else month(getdate())end)

Now I just add the variable into condition: ...

(year(CreationTime)=@yr and MONTH(creationtime)=@mth)

edited Jan 9, 2014 at 15:13

Amicable

3,1113 gold badges54 silver badges80 bronze badges

answered Jan 9, 2014 at 14:37

Douglas Bentley

1

Comments

Arun Prasad E S · Accepted Answer · 2016-07-21 11:27:35Z

0

Combining all conditions

select  a.* from tbl_Company a

where  a.Company_ID NOT IN (1,2)  

AND (   
        (0 = 
            CASE WHEN (@Fromdate = '' or @Todate='')
                THEN 0 
                ELSE 1  
            END
        )      -- if 0=0 true , if 0=1 fails (filter only when the fromdate and todate is present)
                OR
        (a.Created_Date between @Fromdate and @Todate )                 
    )

answered Jul 21, 2016 at 11:27

Arun Prasad E S

10.2k8 gold badges81 silver badges94 bronze badges

Collectives™ on Stack Overflow

How do I do multiple CASE WHEN conditions using SQL Server 2008?

11 Answers 11

3 Comments

Comments

Comments

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

3 Comments

Comments

Comments

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related