Posted by Gert-Jan Strik on 05/31/07 17:11

info@vanoordt.nl wrote:
>
> I like Chris' last idea:
> COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0) +
> 0*COALESCE(Col1, Col2, Col3)
>
> This calculates the value in one expression. I expect it to perform
> well, at least not much worse than without the last term.
>
> Gert-Jan, I need some time to find out what your code does. With all
> respect, it lacks the simplicity of the above solution.

The code assumes that you did not properly normalize your table. It
assumes that Col1, Col2 and Col3 basically have the same meaning, and
should have been modelled as three rows. So the query is transposes the
three columns to three rows. Then the standard behavior of the SUM
aggregate is used, in which means NULLs are skipped. The result will
always be a scalar, and the SUM of an empty set is NULL.

Gert-Jan

[Back to original message]