CASE and DECODE: if-then in SQL

CASE turns conditions into values. It comes in two shapes:

-- searched CASE: any conditions, checked top to bottom, first true one wins
case when runs_batter = 6 then 'six'
     when runs_batter = 4 then 'four'
     else 'other' end

-- simple CASE: compare one expression with values
case extra_type when 'WD' then 'Wide' when 'NB' then 'No-ball' else 'none' end

No ELSE and no match gives null. DECODE is Oracle's older, shorter version of the simple form.

The test case

Sort every delivery of the 2026 final into one outcome: wicket, wide or no-ball, boundary, dot or runs, in that priority order (a wicket off a no-ball counts as a wicket). How many of each?

Expected result

Way 1: searched CASE

select case when wicket_kind is not null         then 'wicket'
            when extra_type in ('WD', 'NB')      then 'wide or no-ball'
            when runs_batter in (4, 6)           then 'boundary'
            when runs_batter + runs_extras = 0   then 'dot'
            else                                      'runs'
       end      as outcome,
       count(*) as deliveries
from   cr_balls_v
where  match_id = 32
group  by case when wicket_kind is not null         then 'wicket'
               when extra_type in ('WD', 'NB')      then 'wide or no-ball'
               when runs_batter in (4, 6)           then 'boundary'
               when runs_batter + runs_extras = 0   then 'dot'
               else                                      'runs'
          end
order  by deliveries desc, outcome;

Order matters. CASE stops at the first true WHEN. Put dot before wicket and every wicket with no runs would be counted as a dot. The order of the WHENs is your priority rule.

(The CASE is repeated in GROUP BY because 19c can't group by an alias. Page 12.2 shows the 26ai shortcut and the inline-view way.)

Way 2: compute once in an inline view

select outcome, count(*) as deliveries
from   (select case when wicket_kind is not null         then 'wicket'
                    when extra_type in ('WD', 'NB')      then 'wide or no-ball'
                    when runs_batter in (4, 6)           then 'boundary'
                    when runs_batter + runs_extras = 0   then 'dot'
                    else                                      'runs'
               end as outcome
        from   cr_balls_v
        where  match_id = 32)
group  by outcome
order  by deliveries desc, outcome;

Way 3: DECODE and SIGN

select outcome, count(*) as deliveries
from   (select decode(sign(nvl(length(wicket_kind), 0)), 1, 'wicket',
                decode(extra_type, 'WD', 'wide or no-ball', 'NB', 'wide or no-ball',
                decode(runs_batter, 4, 'boundary', 6, 'boundary',
                decode(runs_batter + runs_extras, 0, 'dot', 'runs')))) as outcome
        from   cr_balls_v
        where  match_id = 32)
group  by outcome
order  by deliveries desc, outcome;

Same answer, much harder to read. DECODE can only test equality, so "is not null" has to be smuggled in through SIGN(LENGTH(...)). You'll meet DECODE in old code; write CASE in new code. One thing DECODE does better: decode(a, b, ...) treats two nulls as equal, which CASE a WHEN b doesn't.

19c and 26ai

The same on both.

Gotchas

• First true WHEN wins, so order your conditions from most to least specific.
• No ELSE means null for anything you didn't list.
• All branches should return the same type. Mixing 'four' and 4 raises an error.