How can I use the 'NVL' function on a result table?

Question

select (t1.a + t2.b) sum
from (select (aa + bb) a from table_x where cc = 'on') t1, 
     table_y t2 
where t1.id = t2.id

The problem is that when t1 is not found, the final result will be null;

How can I make the default value of t2.b to 0, when t1 is not found?

Thx in advance.

Answer 1

select (t1.a + decode(nvl(t1.a,-1),-1,0,t2.b) sum from (select (aa + bb) a from table_x where cc = 'on') t1, table_y t2 where t1.id = t2.id

Does this work? -1 may need to be replaced by a varchar like 'X' if the t2.b is a varchar selection, which I am guessing is not; looking at the addition here.

Answer 2

You'll need to use a subquery or a left join if you want to actually return of null if it can't find it. Like so:

select
    nvl(
        (select (aa + bb) from table_x where cc = 'on' and id = t2.id)
    , 0) + t2.b as sum
from
    table_y t2

Answer 3

an alternative is this:

select ( nvl(sum(t1.a+t2.b),0)) 
from (select (aa + bb) a from table_x where cc='on') t1, 
      table_y t2
where t1.id = t2.id