How to add null values?

Question

Oracle

There are several fields that need to be added
a1, a2, .. an
each of them can be null
need to find their sum
Interested in a solution of the form
f(a1, a2, .. an)
or
f2(f1(a1, a2, .. an))
or
f(a1 + a2 + .. + an)
but not
f( f1(a1) + f1(a2) + .. + f1(an) )

Answer 1

Use the NVL function

Answer 2

You can create a function using varray, then use it for summing like this:

create type t_varr is varray(100) of number;

create or replace function sum_varr(p_arr t_varr)
return number IS
  v_res number;
begin
  select sum(nvl(column_value,0)) into v_res
  from table(p_arr);
  return v_res;
end;
/

with t as (select 1 a, null b, null c from dual union all
select null a, null b, null c from dual union all
select null a, 2 b, 3 c from dual union all
select 5 a, 2 b, 1 c from dual)
select t.*, sum_varr(t_varr(a,b,c))
from t;