CODEWITHSUNDEEP
sqlsql-servert-sqlcase
dp3
dp3

Reputation: 1677

Conditional CASE statement syntax

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

Upvotes: 6

Views: 13966

Answers (3)

Taryn
Taryn

Reputation: 247650

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

Upvotes: 7

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

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

Upvotes: 2

Josien
Josien

Reputation: 13867

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.

Upvotes: 1

Related Questions