CODEWITHSUNDEEP
sqloracle10g
archavin
archavin

Reputation: 323

Oracle SQL null plus number give a null value

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

Upvotes: 7

Views: 17754

Answers (4)

Ascendant
Ascendant

Reputation: 2579

Just realized none of the answers mentioned what COALESCE() or NVL() is, I'd like to clarify since it can be confusing (I initially thought they were conjunction functions).

So suppose you want to select the full address of places in your table, you have 2 fields - AddressLine1 and AddressLine2. Assume "AddressLine1 " is always NOT NULL, and "AddressLine2" can be NULL. Then you may want to write something like this:

(SQL Server):
SELECT AddressLine1 + COALESCE(AddressLine2, '')
FROM   Places

So that for the entries that AddressLine2= NULL, this wouldn't return a NULL for it.

P.S. I wonder why the plus operation is designed this way in SQL, looks really counterintuitive.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270411

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.

Upvotes: 14

Mike Christensen
Mike Christensen

Reputation: 91666

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.

Upvotes: 4

mrcaramori
mrcaramori

Reputation: 2503

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.

Upvotes: 4

Related Questions