Addition with NULL values

Question

In a stored procedure (Oracle in my case), I want to add some values to an existing record. Problem is that both the existing value and the value to be added can be null. I only want the result to be NULL when both operands are null. If only one of them is null, I want the result to be the other operand. If both are non-null, I want the result to be "normal" addition.

Here's what I am using so far:

SELECT column INTO anz_old FROM aTable Where <someKeyCondition>;
IF anz_old IS NULL
THEN
    anz_new := panzahl;
ELSE
    anz_new := anz_new + NVL (panzahl, 0);
END IF;
UPATE aTabel set column = anz_new Where <someKeyCondition>;

Is there a more elegant way (pereferably completely in SQL, i.e. just in an update statement short of a long CASE-Statement with basically the same logic as the above code)?

Answer 1

If you want to add a and b and either may be null, you could use coalesce, which returns the first non-null parameter you pass it:

coalesce(a+b, a, b) 

So in this case, if neither parameter is null, it will return the sum. If only b is null, it will skip a+b and return a. If a is null, it will skip a+b and a and return b, which will only be null if they are both null.

If you want the answer to be 0 rather than null if both a and b are null, you can pass 0 as the last parameter:

coalesce(a+b, a, b, 0) 

Do consider @erwins answer - null might not be the right thing to be using.

Answer 2

I accomplished it this way:

coalesce("Column1",0.00) + coalesce("Column2",0.00)

I'm working with front end high level execs.... They don't understand why NULL and 0 aren't handled the same way.

In my case it works, just replacing NULLs with 0.00... may not in all though :)

Answer 3

You can also use ISNULL, so if you have 3 values

isnull(val1,0)+isnull(val2,0)+isnull(val3,0)

which ever column will have a NULL will use a 0, otherwise its original value.

Answer 4

In SQL, Null is supposed to be a state that says "I don't know".

If you don't know how much b is, then you also do not know how much a+b is, and it is misleading to pretend that a+b=a in that case.