0

I'm using an ORACLE database & have a table that is all text values (VARCHER2). I need to convert two columns in the table to numbers & then add them together to produce a new column called Total. Unfortunately the data set has NULL values in it & I can't get the columns to add together.

Job_no   |   widget_1   |   widget_2  -  Total
abc123   |   10.00      |   Null      -  10
efg123   |   10.00      |   10.00     -  20
hij123   |   Null       |   10.00     -  10
Lee Murray
  • 315
  • 1
  • 6
  • 19
  • Didi you try with NVL() ? – Giacomo Degli Esposti Feb 01 '17 at 10:20
  • Why not use number datatype? – BobC Feb 01 '17 at 11:28
  • Is NULL meant to represent 0? Then why doesn't your input data show 0, instead of NULL? And, what do you mean by "can't get the columns to add together"? You can, but the result will be NULL if at least one term is NULL, and that is the **correct answer** unless NULL in the base table was wrong in the first place and it should have been 0. –  Feb 01 '17 at 13:47
  • Unless...... Are you saying the value in the columns is a four-letter word, `'Null'`, and not actually `NULL` (which may be represented, in Oracle, as the empty string `''`)? THAT would indeed be a problem! –  Feb 01 '17 at 13:48

1 Answers1

0

You can use COALESCE:

with yourTable(Job_no, widget_1, widget_2) as (
    select 'abc123',10.00,Null  from dual union all
    select 'efg123',10.00,10.00 from dual union all
    select 'hij123',Null ,10.00 from dual
)
/* the query *
select Job_no, widget_1, widget_2, coalesce(widget_1, 0) + coalesce(widget_2, 0) as total
from yourTable

In Oracle you could even use the Oracle-specific NVL:

select Job_no, widget_1, widget_2, NVL(widget_1, 0) + NVL(widget_2, 0) as total
from yourTable

Here you find something more on COALESCE vs NVL

Community
  • 1
  • 1
Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • This assumes that `null` is meant to represent 0. That is a dangerous assumption in general. `null` is a valid entry ("value" but actually `null` means very specifically "lack of value") for the `number` data type, just as it is for every other data type. And if the "value is unknown" as a string it is also "unknown" as a number, and the result of adding it to another number is also "unknown". –  Feb 01 '17 at 13:45