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|
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.
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.