Redshift nested case when/ If then else

user_15361369 0
Viewed 4044

Is there a way of nesting case when statements when one has multiple conditions. For example below, where num_students = 2, there are many diff classes. Thus the else statement for num_of students =2 is 'Stu_2, but for the overall data is 'unk'

select id, test_a, test_b,
case when num_students = 5 then 'Stu_5'
when num_students = 4 then 'Stu_4'
when num_students = 3 then 'Stu_3'
  when num_students = 2 and class = 'Eng' then 'Stu_Eng_2'
  when num_students =2 and class = 'Fre' then 'Stu_Fre_2'
  when num_students = 2 and class = 'His' then 'Stu_His_2'
  when num_students =2 and class = 'Geo' then 'Stu_Geo_2'
  else 'Stu_2'
else 'unk'
from table
amazon-redshift sql
2 Answers

Yep, that's one of the purposes of nesting case statements. When you nest them, you need to call the CASE statement again as follows:

select 
  id, 
  test_a, 
  test_b,
  case 
    when num_students = 5 then 'Stu_5'
    when num_students = 4 then 'Stu_4'
    when num_students = 3 then 'Stu_3'
    when num_students = 2 then
      case 
        when class = 'Eng' then 'Stu_Eng_2'
        when class = 'Fre' then 'Stu_Fre_2'
        when class = 'His' then 'Stu_His_2'
        when class = 'Geo' then 'Stu_Geo_2'
        else 'Stu_2' 
      end
    else 'unk' 
  end as <column_name>
from <table_name>

Don't forget the END clause after each nested CASE WHEN :)

user_115329190

You can phrase this in a different order:

(case when num_students = 5 then 'Stu_5'
      when num_students = 4 then 'Stu_4'
      when num_students = 3 then 'Stu_3'
      when num_students <> 2 or num_students is null then 'unk'
      -- num_students must be 2
      when class = 'Eng' then 'Stu_Eng_2'
      when class = 'Fre' then 'Stu_Fre_2'
      when class = 'His' then 'Stu_His_2'
      when class = 'Geo' then 'Stu_Geo_2'
      else 'Stu_2'
 end)
user_11440350
Related