Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm doing some calculation using (+ operations), but i saw that i have some null result, i checked the data base, and i found myself doing something like number+number+nul+number+null+number...=null . and this a problem for me.

there is any suggestion for my problem? how to solve this king for problems ? thanks

share|improve this question
3  
Use NVL function to substitue null with some other value, say 0. – Horrendous_Space_Kablooie Jun 7 at 16:17
@Horrendous_Space_Kablooie, you should put this as the answer. Because this is what they need to do. There is no other option – Steve Jun 7 at 16:18
Perfect (Y) it help me – archavin Jun 7 at 16:23

3 Answers

up vote 5 down vote accepted

My preference is to use ANSI standard constructs:

select coalesce(n1, 0) + coalesce(n2, 0) + coalesce(n3, 0) + . . .

NVL() is specific to Oracle. COALESCE() is ANSI standard and available in almost all databases.

share|improve this answer
yes it's a good suggestion, maybe one of the best, now i know there are two options NVL() and COALESCE() but this last is standard. thanks a lot @Gordon Linoff – archavin Jun 7 at 16:25
up vote 2 down vote

You need to make those values 0 if they are null, you could use nvl function, like 

 SELECT NVL(null, 0) + NVL(1, 0) from dual;

where the first argument of NVL would be your column.

share|improve this answer
Thanks it help me – archavin Jun 7 at 16:23
up vote 1 down vote

You can use something like NVL to cast a null into a value, such as 0.

select NULL + 5 from dual;

Will return null.

select NVL(NULL, 0) + 5 from dual;

Will return 5.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.