Hugo Kornelis (12/15/2010)
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.
In the last few years of working here, I've used AVG only a handful of times. I typically divide totals with other totals to get averages. There is an assumption that has to be made that the data is there for most of my work; e.g., total hours worked for office x in y time frame = ? When summing those hours, the last thing I need to worry about are NULLs. But I do work in a warehouse with the data has been verified and massaged. I work in finance and if something isn't right, it doesn't balance, so that helps too. But if I want to add column A to column B in my temp table, I sure don't want NULLs tripping me up. So in my circumstances, NULLs hurt more than help.
The opposite could also be asked... Why do you have missing data?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking