Geek Answers Handbook

Conditional CASE statement syntax

I need help writing a case statement to represent. The base table has 2 columns that I will refer to: "Stage" and "YesNo".

If the Stage column is 1 and the YesNo column is 1, I need a CASE statement to show it in the view as No. If the Stage column is 1 and the YesNo column is 0, I need a CASE statement to show it in the view as “Yes”. If the Stage column is 1 and the YesNo column is NULL, I need a CASE statement to show it in the view as NULL. If Stage has a value other than 1, I need the YesNo column to display as NULL in the view.

This is my logic so far, I think it is correct, but when I try to start it, I get a syntax error regarding 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
+6
source share
3 answers

Remove ELSE WHEN , if you leave ELSE , then it will return null for any elements that do not match 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
+6
source 
 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
+2
source

You are using ELSE WHEN , it must 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

Read more about the msdn page on CASE .

+1
source

More articles:


All Articles