42

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

Improve this question

5 Answers 5

Reset to default
84

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The Coalesce answer is interesting. For more than two values, or a different logical look, I like: ISNULL(a,0.0) + ISNULL(b,0.0) + ISNULL(c,0.0)
I guess ISNULL is for T-SQL, Coalesce or NVL would be suited to Oracle as used in the question.
@Mister_Tom Your answer will return 0 for all inputs null. My coalesce(a+b, a, b) will return null, which may or may not be what is wanted. If you wanted it to return 0, you could use coalesce(a+b, a, b, 0). I've edited my answer to add this extra option.
The only trouble is that if you're looking for a+b+c+d+e+f, then this is a significantly worse solution than the one below.
13

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

Improve this answer

2 Comments

This answer will return 0 if both values are null. My answer will return null. Depending on what you are doing, that may be preferable (or it may not - this is a good answer).
@fancyPants This answer is more readable than the accepted answer imo
13

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.

Improve this answer

2 Comments

This is the best answer, since can be used in any situation. COALESCE() option is useful for A+B. But if you want to A+B+C+D+... it makes no sense trying to use it. ISNULL() is always working.
Since the orginal answer doesn't specify any SQL engine: ISNULL() works for SQL server, IFNULL() for MySQL and NVL() for Oracle. Details at w3schools.com/sql/sql_isnull.asp
10

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.

Improve this answer

4 Comments

True, but it might be the best estimate that you have.
Well, we can get into the debate of don't know, not applicable etc. for NULL. In my case, NULL corresponds best to n/a .. so if I have a Non-NULL value and a NULL, it is fair to use the value .. it maps best to the what I'm modeling.
It doesn't matter what Null is supposed to be in SQL, it matters what Null means in the code or in reporting.
If "it matters what Null means in the code (/application)" then the right thing to do for SQL when it has to do an operation involving a Null, is to go out to the application and ask. That option not being available, SQL implementers need to choose themselves, document their choice, and hope application designers will then make informed decisions about introducing NULL in their designs, and about coding operations that potentially involve those NULLs. The choice made is for NULL to represent "unknown". "Not applicable" is just the designer betraying his own incompetence.
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.