Do you have a seek sense? Can you see dead code?

The original query is redacted to protect the innocent.

What's wrong with the following query? Can you spot the dead code?

create table tosses
(
  attempt int identity(1,1) primary key,
  fate char(1),
  constraint ck_tosses check(fate in ('H','T') or fate is null)
);

insert into tosses(fate) values
('H'),
('H'),
(null),
('T'),
(null),
('H'),
('T');

select
    attempt,
    fate,
    case fate 
    when 'H' then 'Head' 
    when 'T' then 'Tail'
    when null then 'Fate not yet determined'
    else 'http://9gag.com/gag/4380545'
    end as fate_result
from tosses

Live code: http://www.sqlfiddle.com/#!3/6a61e/2

The output of that is this:

| ATTEMPT |   FATE |                 FATE_RESULT |
--------------------------------------------------
|       1 |      H |                        Head |
|       2 |      H |                        Head |
|       3 | (null) | http://9gag.com/gag/4380545 |
|       4 |      T |                        Tail |
|       5 | (null) | http://9gag.com/gag/4380545 |
|       6 |      H |                        Head |
|       7 |      T |                        Tail |

The dead code is the WHEN NULL, the else part on the query above is just a decoy ツ To correct the dead code, we should change the WHEN NULL to WHEN fate IS NULL

select
    attempt,
    fate,
    case when fate is null then 'Fate not yet determined'
    else
        case fate 
        when 'H' then 'Head' 
        when 'T' then 'Tail'
        else 'http://9gag.com/gag/4380545'
        end
    end as fate_result
from tosses

Live code: http://www.sqlfiddle.com/#!3/6a61e/3

Output:

| ATTEMPT |   FATE |             FATE_RESULT |
----------------------------------------------
|       1 |      H |                    Head |
|       2 |      H |                    Head |
|       3 | (null) | Fate not yet determined |
|       4 |      T |                    Tail |
|       5 | (null) | Fate not yet determined |
|       6 |      H |                    Head |
|       7 |      T |                    Tail |  

You can also do the other form of CASE expression:

select
    attempt,
    fate,

    case
    when fate = 'H' then 'Head'
    when fate = 'T' then 'Tail'
    when fate is null then 'Fate not yet determined'
    else 'http://9gag.com/gag/4380545'    
    end as fate_result

from tosses

Live code: http://www.sqlfiddle.com/#!3/6a61e/4

Output:

| ATTEMPT |   FATE |             FATE_RESULT |
----------------------------------------------
|       1 |      H |                    Head |
|       2 |      H |                    Head |
|       3 | (null) | Fate not yet determined |
|       4 |      T |                    Tail |
|       5 | (null) | Fate not yet determined |
|       6 |      H |                    Head |
|       7 |      T |                    Tail |  

On both form of the correct query above, the dead code in that query is no longer the NULL scenario, the dead code is the ELSE part as we have CHECK constraint in place.


Happy Coding! ツ