mtillman-921105 (12/15/2010)
Mike Dougherty-384281 (12/14/2010)
mtillman-921105 (12/14/2010)
Good question - thanks! NULLs can be hazardous, they should come with a warning.In the case of "Null value is eliminated by an aggregate or other SET operation" the trick is to use nulls without the warning.
hint: set ANSI_WARNINGS OFF (and accept responsibility for the consequences)
My solution is SET myColumn = ISNULL(myColumn, 0) everywhere. NULLs are more trouble than they're worth more often than not when dealing with financial data. 😉 (Yes, I know that a NULL can mean that the data's not right. But more often than not, a zero tells me the same thing anyway.)
Wow!
And how do you then distinguish a "missing data" 0 from a "number zero" 0?
BTW, if I'm running AVG over a column, I'd rather have a warning and the average of all non-missing values, than a number that is way too low because the missing values are replaced with 0.