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 »
My Bees!
Forenoon watch, 4 bells (10:16 am)

I found a wasp nest under the eave of my house the other day.

Let me put this in perspective, though—I found a wasp nest as big as my head under my eave the other day.

I've been engaging them and their queen in chemical warfare. I needed to haul a ladder out so the sprays at least twenty feet bee spray can spray up the 18 or 20 feet to the nest. Apparently you can only "spray at least 20 feet" when you spray down… 🙂

Anyway, last night I really got them stirred up, there was buzzing everywhere. Fortunately for me, most of it was on the ground, writhing in bee-pain. I couldn't get back inside for about 15 minutes due to the sheer amount of bees swarming around my front door. Finally, most of them died off and I was able to head inside to safety.

I hosed down the nest this morning, about half of it fell off the house. Whatever bee-glue they used to stick it on is still working for the honeycomb apartments where they lived, though. I'm going to have to get a big stick and poke it until it drops off, I can't have any new bees moving in unless they pay me rent.

2 Comments »