Lord Nulldemort

In honor of tomorrow’s midnight release of Harry Potter and the Deathly Hallows Part 2, I thought it would be appropriate to share a quick spell that recently aided me in one of my frequent battles with That-Which-Has-No-Value, or as I timidly call it when nobody is around to hear me whisper, Lord Nulldemort.

I was writing a query that required me to check two columns for values and decide which one to use; one of the columns was always NULL, the other always had the value I needed:

Column A	Column B
1	NULL
NULL	2
3	NULL

Within the larger query, I wrote a simple CASE statement to choose the appropriate column values:

CASE A
	WHEN NULL THEN [B]
	ELSE [A]
	END
AS NEW_COLUMN

Strangely, the query returned 1, NULL, 3 for the value of NEW_COLUMN rather than the expected values of 1, 2, 3.  I tried variations of this query (for example, using ‘WHEN IS NULL’), but no dice.

…

ACCIO GOOGLE!

…

Via the magic of the Internet, I learned that testing a single column’s value with a CASE statement doesn’t correctly test for NULL values.  I changed the query as follows:

CASE
	WHEN [A] IS NULL THEN [B]
	WHEN [B] IS NULL THEN [A]
	END
AS NEW_COLUMN

Notice that rather than using the CASE statement to explicitly test the value of Column A, I created a generic CASE statement that tests both [A] and [B].

The moral of the story is that NULL values are the mutant spawn of the Dark Lord , and while I’m sure there is a logical explanation for this phenomenon, I’m comfortable just chalking it up to dark magic.

Tune in tomorrow when I scrupulously test my staying awake at work the day after the Harry Potter premiere charm.

UPDATE: A friend called my attention to the COALESCE statement in SQL, which accomplishes my mission in a single, elegant statement.  Still claiming dark magic, though.