Addition with NULL values

IronGoofy_25320 0
Viewed 77922

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)?

null sql
5 Answers

In SQL terms, when adding numbers, a result of NULL means there were no non-null numbers added.

This suggests that a sensible answer in SQL terms would be

CASE WHEN A IS NULL AND B IS NULL THEN NULL ELSE ISNULL(A, 0) + ISNULL(B, 0) END

user_110938320
Related