Dealing with NULLs in SQL
Posted in Code
Afternoon watch, 7 bells (3:59 pm)

I ran across a nasty problem today that MS, a former cow-orker, failed to solve. And failed to notice. This isn't a huge deal, but things aren't getting reported right because of it.

Here are the basics:
Three fields of type money are being SELECTed from a table. Sometimes one of them is NULL. Two of the fields are being added together, and one is being subtracted from that sum.

The problem is, any time a NULL value is present, then entire mathematical operation evaluates to NULL as well.

Through some research, I discovered Microsoft's T-SQL CASE keyword:

SELECT (Tax +
  CASE WHEN Freight IS NULL
  THEN '0.00' ELSE Freight
  END -
  Discount) AS 'Cost'
FROM myTable

Of course, the fields Tax, Freight, and Discount are all the in the table myTable.

This actually works quite nicely. The problem was tracking down the information. I'm posting it here mainly for my own use later, when I forget how to do something weird like this.

Normally I'd just set the NULL values to 0.00 and forge on ahead, but in this case I have no idea what I may be breaking somewhere else and needed a simple, non-intrusive peek into the database.

2008 April 25 Note: Today I needed this information. I'm a genius.

Leave a Comment »

Leave a Reply