Is there an Oracle function to return boolean if either one of the two value is not null?

Question

for example: n1 and n2, wonder any oracle built in function could achieve that?

Sample condition and expected output:

1. if n1 is null and n2 is null, return null
2. if n1 is null and n2 is not null, return n2
3. if n1 is not null and n2 is null, return n1
4. if n1 and n2 is not null, return summation of n1 and n2

Answer 1

Check this:

case when n1 is not null and n2 is not null then
  n1 + n2
else
   COALESCE(n1,n2)
end

---

With single coalesce

 COALESCE(n1+n2,n1,n2)

create table t as 
 select 1 n1, 2 n2 from dual union all
 select null, 2 from dual union all
 select 1, null from dual union  all
 select null, null from dual

4 rows affected

select COALESCE(n1+n2,n1,n2) from t

| COALESCE(N1+N2,N1,N2) |
| --------------------: |
|                     3 |
|                     2 |
|                     1 |
|                  null |

db<>fiddle here

Answer 2

This could be done using a combination of a CASE expression and coalesce():

case 
   when n1 is null and n2 is null then null
   else coalesce(n1, 0) + coalesce(n2, 0)
end as n1_n2_sum

Answer 3

You can try the below -

with data as (
  select 1 X, 2 Y, 1 sno from dual
  union
  select null X, 2 Y, 2 sno from dual
  union
  select 1 X, null Y, 3 sno from dual
  union
  select null X, null Y, 4 sno from dual
  )
  select sno, X, Y, coalesce(X+Y,X,Y) from data
  order by sno


+-----+--------+--------+-------------------+
| SNO |   X    |   Y    | COALESCE(X+Y,X,Y) |
+-----+--------+--------+-------------------+
|   1 | 1      | 2      | 3                 |
|   2 | (null) | 2      | 2                 |
|   3 | 1      | (null) | 1                 |
|   4 | (null) | (null) | (null)            |
+-----+--------+--------+-------------------+