[Null] + Number Yields [Null]: Auto-cast to Zero?

[ScottS28]

This is not a bug but figured I'd check just in case anyone has some thoughts or insights.

If I have two numeric fields, add them together, and store the result in a third, the resultant field will be null whenever either of the two addends are null. I'm sure this is working as intended but catches me by surprise -- I'd expect the result to be 0 if both fields are null, or else if only one field is null, to be the value of the other field. (Unfortunately I've developed this habit/expectation from working in other languages / environments that automatically cast null values to 0 when performing numeric arithmetic.)

I know I can either convert the null values to 0 before the addition, or else change the formula for the definition of the third field to check for nulls in either addend. (Or probably several other ways to resolve!) I just often forget to do this proactively, and I've lost a few cumulative hours at this point in my career debugging this issue across multiple workflows. I also don't like using the Cleanse tool on numeric fields because null and 0 often have different meanings, so I prefer to preserve the null values.

Any way to achieve this "auto cast" behavior or do I just have to make sure that I'm always careful around null values?

[MarqueeCrew]

@ScottS28 ,

True.  That's what you get for adding Null values.

Max([Field1],0) + Max([Field2],0)

You can either replace all Nulls with 0 (you can use the Data Cleansing macro) or use a formula like the one above.

Cheers,

Mark

[ScottS28]

Thanks for the confirmation! I did figure this was simply part of the
definition of the data type of null, but it’s interesting that the max
function is well behaved with respect to null when the addition operator is
not.