Oracle - you so crazy!

One of the tasks I was given recently was to check the credit card retention data and see how the data looks and I discovered a very interesting thing with the Case statement and NULL in Oracle. My initial query seemed to indicate that the retention job was not running. Here's the statement I ran:

select case card_number when null then 'NULL' else 'NOT NULL' end as card, count(*)
from credit_card
group by case card_number when null then 'NULL' else 'NOT NULL' end
order by 1;

This looks pretty straightforward. The case is on the card_number when the value is null change the output to the string 'NULL' else change the output to 'NOT NULL' and count the occurrences of each.
Every time I ran this in every database, it showed all records as 'NOT NULL' which shouldn't be correct. I circled back and changed the case to a decode to see what it would look like. That statement was:

select decode(card_number,null,'NULL','NOT NULL') as card, count(*)
from credit_card
group by decode(card_number,null,'NULL','NOT NULL')
order by 1;

The logic is the same but we use an oracle standard decode function instead of the CASE keyword. And our results look more like we'd expect. Some are null and some are not null. So, what went wrong with the CASE statement? Some of you might have already picked up on what I did wrong. The null in the ...Case card_number when null... part was not checking if card_number was null, it was saying when there is nothing to do change the variable to 'NULL'. If we look to the NULL statement in PL/SQL that might help us understand.

create or replace procedure validnullproc as
Begin
Null;
End;

This is a valid procedure that does fuck all. So basically I was inserting a null chunk of code into my Case statement. Here is a statement that shows a couple of other options:

SELECT
  CASE card_number
    WHEN NULL
    THEN 'NULL'
    ELSE 'NOT NULL'
  END AS card,
  DECODE(card_number,NULL,'NULL','NOT NULL') as decodecard,
  nvl2(card_number, 'NOT NULL', 'NULL') as nvl2card
FROM credit_card
WHERE card_number IS NULL
AND rownum         < 11
UNION
SELECT
  CASE card_number
    WHEN NULL
    THEN 'NULL'
    ELSE 'NOT NULL'
  END AS card,
  DECODE(card_number,NULL,'NULL','NOT NULL'),
  nvl2(card_number, 'NOT NULL', 'NULL')
FROM credit_card
WHERE card_number IS NOT NULL
AND rownum         < 11;

Results:
"CARD"                        "DECODECARD""NVL2CARD"
"NOT NULL"                    "NOT NULL"                    "NOT NULL"                  
"NOT NULL"                    "NULL"                        "NULL"                      

So, my fancy Case statement to pull the CARD column above is basically just saying always Print 'NOT NULL'. However, both the DECODE and the NVL2 functions resolve to the correct values and I think NVL2 is the better option in this case. And I must admit that NVL2 is new to me, personally.

 In my investigations into the odd behavior of the CASE and NULL statement, I did uncover this lovely new function called NVL2. It is basically DECODE for NULLs. Most of you are probably familiar with the standard NVL function. You might have seen something like NVL(status_code, 'X') which will resolve to the status_code value unless it is NULL in which case it replaces the NULL with 'X'. You can do it with DECODE but it is more typing: DECODE(status_code, null, 'X', status_code). DECODE can come in real handy when you are displaying coded data, especially in older somewhat denormalized or poorly designed enterprise applications. Say you have a status column on some table that stores single letter status codes but you did not store the corresponding word values in a reference table. You can use DECODE in your report SQL to do this for you: DECODE(status, 'A', 'Active', 'I', 'Inactive', 'C', 'Closed', 'W', 'Whiskey', T', 'Tarngo', 'F', 'Foxtrot'). As you can see from the above example, you can handle NULL/NOT NULL with decode as well but NVL2 is designed specifically to allow you to change the value of not null data. As you can probably tell from the above example, the syntax is the function name NVL2 and the first parameter is the field or value you are changing, the second parameter is the value you want to output if the first parameter is not null, and the final parameter is the value you want to output if the first parameter is null.

Well, thanks for tuning into my adventure's with Oracle. Now, go forth and use Oracle wisely.