2008-03-12

NULL values in SQL Server subqueries

I'm no SQL query expert, though I do need to crunch through some reports on occasion. Here's one that drove me nuts.

I have a freight amount that is either zero or positive. It's never negative, and it's never NULL. In some cases, we would wipe out the value on an invoice based on a freight allowance. Both the freight and allowance values are in a separate table from the invoice headers.

My goal is a report that puts the actual freight amount into an output file for later analysis. The invoice header table only has a summary of all miscellaneous changes, so I have to go back to the misc charge table to get the freight... and the allowance, if it exists.

Aye, there's the rub.

It's not really hard, or at least it shouldn't be. I return the value via a subquery. And there's a nice function called coalesce(). It works like this:

coalesce(value1, value2, valN)

...where valN is the value you want to return if the value is NULL. Not so fast, my freind! I had to wrap the subquery in the coalesce function rather than put the function in the subquery:


BAD

select
invoice
(select
coalesce(value, 0)
from table2
where misccharge = 'Allowance'
) as frtallowance
from table1
where invoicedate = today


GOOD

select
invoice
coalesce((select
value
from table2
where misccharge = 'Allowance'
), 0) as frtallowance
from table1
where invoicedate = today


That'll matter to someone somewhere one day.

2008-03-11

When You Do A "Select Case UCASE"...

When You Do A "Select Case UCASE" in VB and match something on it, you really need to match the UCASE in the matching statement. Otherwise, people suffer and hair is surreptitiously removed by the handful... 'cause you'll be the only one aware that you're pulling out your hair. The only evidence your co-workers will see will be you... sans locks, albeit in patchy, embarrasing spots, finally emerging from your cubicle, spouting phases like "I'm such and idiot!"