Case with multiple conditions

  • I need to change returned value, from select statement, based on several conditions. I tried something like that:

    ,CASE i.DocValue
      WHEN 'F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal
      ELSE 0
    END as Value
    

    why it is not working and what is recommended way to do this ? There will be next several WHEN conditions.

    Thank you

  • ,CASE WHEN i.DocValue ='F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal
      ELSE 0
    END as Value
    

    There are two types of CASE statement, SIMPLE and SEARCHED.

    You cannot evaluate multiple expressions in a Simple case expression, which is what you were attempting to do.

    -- Simple CASE expression: 
    CASE input_expression 
         WHEN when_expression THEN result_expression [ ...n ] 
         [ ELSE else_result_expression ] 
    END 
    -- Searched CASE expression:
    CASE
         WHEN Boolean_expression THEN result_expression [ ...n ] 
         [ ELSE else_result_expression ] 
    END
    

    Example of Simple CASE:

    CASE x
      WHEN 'a' THEN 'b'
      WHEN 'c' THEN 'd'
      ELSE 'z'
    END
    

    Example of a Searched CASE:

    CASE 
      WHEN x = 1 AND y = 2 THEN 'a'
      WHEN x = 2 AND y = 1 THEN 'b'
      ELSE 'z'
    END
    

    Further Reading: http://msdn.microsoft.com/en-us/library/ms181765.aspx

  • You can write like this one too if you have multiple requirements:

    CASE WHEN CITY='HYDERABAD' THEN       
     CASE WHEN AREA='BHILL' THEN PIN='12345'      
     WHEN AREA='DSNR' THEN PIN='67890'          
     ELSE '33333'          
     END           
    END AS PIN_COD
    

    you are right, nested case for complex condition works too :)

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM