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 ```

Accepted Answer

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
```

Conditional CASE statement syntax

Solution

Problem