Conditional CASE statement syntax

Question

I need help writing a case statement for a view. The base table has 2 columns that I'll be referencing: 'Stage' and 'YesNo'.

If Stage column is 1, and the YesNo column is 1, I need the CASE statement to show it in the view as 'No.' If the Stage column is 1, and the YesNo column is 0, I need the CASE statement to show it in the view as 'Yes.' If the Stage column is 1, and the YesNo column is NULL, I need the CASE statement to show it in the view as NULL. If the Stage is anything other than 1, I need the YesNo column to show in the view as NULL.

This is my logic so far which I think is correct, but when I try to run it, I get a syntax error about the word 'AS'. Any suggestions?

CASE 
    WHEN a.Stage = 1 and a.YesorNo = 1 THEN 'No' 
    ELSE WHEN a.Stage = 1 and a.YesorNo = 0 THEN 'Yes' 
END AS NewViewColumn

Solution 1

Remove the ELSE WHEN, if you leave the ELSE out then it will return null for any items not meeting the remaining logic:

CASE 
    WHEN a.Stage = 1 and a.YesorNo = 1 THEN 'No' 
    WHEN a.Stage = 1 and a.YesorNo = 0 THEN 'Yes' 
END AS NewViewColumn

Or use:

CASE 
    WHEN a.Stage = 1 and a.YesorNo = 1 THEN 'No' 
    WHEN a.Stage = 1 and a.YesorNo = 0 THEN 'Yes' 
    ELSE 'other'
END AS NewViewColumn

Solution 2

CASE 
  WHEN a.Stage = 1 and a.YesorNo = 1 THEN 'No'   
  WHEN a.Stage = 1 and a.YesorNo = 0 THEN 'Yes'
  ELSE something else  -- If you ignored this it will be NULL
END AS NewViewColumn

Solution 3

You are using ELSE WHEN, this should be either ELSE or WHEN .. THEN ..:

CASE
  WHEN a.Stage = 1 and a.YesorNo = 1 THEN 'No'
  ELSE 'Yes'
END AS NewViewColumn

Or:

CASE
  WHEN a.Stage = 1 and a.YesorNo = 1 THEN 'No'
  WHEN a.Stage = 1 and a.YesorNo = 0 THEN 'Yes'
END AS NewViewColumn

Check out the msdn page on CASE for more information.