IFF is a conditional expression Snowflake function that evaluates to a Single-level if-then-else expression. However IFF only allows a single condition. What if you need to implement a if-then-elseif-else-endif? Most people will opt to use CASE STATEMENT. While CASE STATEMENT will do the trick, it is not most optimal solution for a if-then-elseif-else-endif or even a if-then-elseif-else-if-else-endif conditional expression. DECODE function is a versatile Snowflake Conditional Expression Function that lets you implement if-then-elseif-else-if-else-endif expression without the use of a CASE STATEMENT. Here is an example:

Raw Data

create or replace table orders (order_date date, item_id number, amount number);

insert into orders values 
  ('2022-11-21', 1, 20)
  , ('2022-11-22', 1, 20)
  , ('2022-11-23', 1, 20)
  , ('2022-11-23', 1, 20)
  , ('2022-11-23', 1, 20)
  , ('2022-11-24', 1, 20)
  , ('2022-11-25', 1, 20)
  , ('2022-11-25', 1, 20)
  , ('2022-11-25', 1, 20)
  , ('2022-11-25', 1, 20)
  , ('2022-11-25', 1, 20)
  , ('2022-11-26', 1, 20)
  , ('2022-11-27', 1, 20)
  , ('2022-11-27', 1, 20)
  , ('2022-11-28', 1, 20)
  , ('2022-11-29', 1, 20)
  , ('2022-11-30', 1, 20)
  , ('2022-11-30', 1, 20);

DECODE conditional expression function to implement if-then-elseif-else-endif

select
  order_date::date
  , decode(
    extract('dayofweek_iso', order_date)
    , 1, '1 - Monday'
    , 2, '2 - Tuesday'
    , 3, '3 - Wednesday'
    , 4, '4 - Thursday'
    , 5, '5 - Friday'
    , 6, '6 - Saturday'
    , 7, '7 - Sunday'
  ) as day_of_week
  , sum(amount)
from orders
where order_date::date between '2022-11-21' and  '2022-11-27'
group by 1, 2
order by day_of_week
ORDER_DATE::DATE DAY_OF_WEEK SUM(AMOUNT)
2022-11-21 1 - Monday 20
2022-11-22 2 - Tuesday 20
2022-11-23 3 - Wednesday 60
2022-11-24 4 - Thursday 20
2022-11-25 5 - Friday 100
2022-11-26 6 - Saturday 20
2022-11-27 7 - Sunday 40

image

See also