Multiple conditions in CASE WHEN statement

Question

I'm working in SQL (Amazon Redshift) with Recruitment data where each applicant has multiple sources, which I've split into different columns called source_1, source_2, and source_3, along with a number_of_sources column to record how many sources that applicant has. We want each applicant to only have one source, and have certain rules to follow in how to select which source, and I'm trying to write a CASE WHEN statement to achieve this. I'm currently getting an error but I can't see why.

My code is:

CASE WHEN number_of_sources = 1
THEN source_1
  ELSE WHEN number_of_sources = 2
   and source_1 in ('Email Applicant', 'Job site', 'Added manually') THEN source_2
  ELSE WHEN number_of_sources = 2
   and source_2 in ('Email Applicant', 'Job site', 'Added manually') THEN source_1
  ELSE WHEN number_of_sources = 3
   and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
   and source_2 in ('Email Applicant', 'Job site', 'Added manually')  THEN source_3
  ELSE WHEN number_of_sources = 3
   and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
   and source_3 in ('Email Applicant', 'Job site', 'Added manually')  THEN source_2
  ELSE WHEN number_of_sources = 3
   and source_3 in ('Email Applicant', 'Job site', 'Added manually') 
   and source_2 in ('Email Applicant', 'Job site', 'Added manually')  THEN source_1
    ELSE 'Blank'    END                                                                             as source_final
from staging.candidates_by_source

Hope that makes sense, any advice would be appreciated! Thanks so much

Answer 1

remove all else except the last one

CASE WHEN number_of_sources = 1
THEN source_1
   WHEN number_of_sources = 2
   and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
 THEN source_2
  WHEN number_of_sources = 2
   and source_2 in ('Email Applicant', 'Job site', 'Added manually') 
THEN source_1
  WHEN number_of_sources = 3
   and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
   and source_2 in ('Email Applicant', 'Job site', 'Added manually') 
  THEN source_3
  WHEN number_of_sources = 3
   and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
   and source_3 in ('Email Applicant', 'Job site', 'Added manually')
  THEN source_2
   WHEN number_of_sources = 3
   and source_3 in ('Email Applicant', 'Job site', 'Added manually') 
   and source_2 in ('Email Applicant', 'Job site', 'Added manually')
   THEN source_1
    ELSE 'Blank'    END                                                                               as source_final
from staging.candidates_by_source