multiple case when statement with DISTINCT-CodePudding

I have the below CASE WHEN statement that I am trying to run to re-name my stages from the Opportunity table but I cannot quite get it to work...

SELECT DISTINCT  op.[StageName],
CASE WHEN   op.[StageName]  = 'Conversation/Idea' THEN '5.Conversation/Idea' OR
CASE WHEN   op.[StageName]  = 'RFP/Brief Stage' THEN '4.RFP/Brief Stage' OR
CASE WHEN   op.[StageName]  = 'RFI Stage' THEN '3.RFI Stage' OR
CASE WHEN   op.[StageName]  = 'Pitched' THEN '2.Pitched' OR
CASE WHEN   op.[StageName]  = 'Shortlisted' THEN '1.Shortlisted'
END 
FROM [Opportunity]op

Can anyone advise on what is wrong / missing.

Many thanks,

CodePudding user response：

If you drop the "OR"s and drop the redundant "CASE"s, then it should work.

SELECT DISTINCT  op.[StageName],
CASE WHEN   op.[StageName]  = 'Conversation/Idea' THEN '5.Conversation/Idea'
     WHEN   op.[StageName]  = 'RFP/Brief Stage' THEN '4.RFP/Brief Stage'
     WHEN   op.[StageName]  = 'RFI Stage' THEN '3.RFI Stage'
     WHEN   op.[StageName]  = 'Pitched' THEN '2.Pitched'
     WHEN   op.[StageName]  = 'Shortlisted' THEN '1.Shortlisted'
END 
FROM [Opportunity]op

CodePudding user response：

Try this

SELECT DISTINCT  op.[StageName],
CASE WHEN   op.[StageName]  = 'Conversation/Idea' THEN '5.Conversation/Idea'  
WHEN   op.[StageName]  = 'RFP/Brief Stage' THEN '4.RFP/Brief Stage' 
WHEN   op.[StageName]  = 'RFI Stage' THEN '3.RFI Stage'  
WHEN   op.[StageName]  = 'Pitched' THEN '2.Pitched'  
WHEN   op.[StageName]  = 'Shortlisted' THEN '1.Shortlisted'
END 
FROM [Opportunity]op