Oracle-nvl和nvl2函式

NVL函式的用法：

NVL（expr1,expr2），表示如果expr1的值為NULL時，則返回expr2的值，否則返回expr1的值。

NVL2函式的用法：

NVL2（expr1,expr2,expr3），檢查表示式expr1，表示如果expr1不為NULL時，返回值為expr2，否則返回值為expr3。

select t1.hostno, t2.times, nvl(t2.cost, 0) as cost
  from (select p.hostid, p.hostno
          from position_host_info p
          left join position_host_info_extend e
            on p.hostid = e.hostid
         where e.verify_status = 1
           and e.process_status <> 4
           and e.process_status <> 5
           and p.appsysid = 23551
           and p.appdeptid = 3) t1
  left join 
  (select p.hostno as hostNo,
                    t.host_id,
                    sum(nvl2(t.DECORATE_COST, 1, 0)) as times,
                    sum(nvl(t.DECORATE_COST, 0)) as cost
               from position_host_info p
               left join position_host_info_extend e
                 on p.hostid = e.hostid
               left join COST_DECORATE t
                 on p.hostid = t.host_id
                and t.PAYMENT_DATE >= to_date('2017-03-20', 'yyyy-mm-dd')
                and t.PAYMENT_DATE <= to_date('2017-03-24', 'yyyy-mm-dd')
              where p.appsysid = 23551
                and p.appdeptid = 3
                and e.verify_status = 1
                and e.process_status <> 4
                and e.process_status <> 5
                and t.apply_status = 11
              group by p.hostno, t.host_id) t2
    on t1.hostid = t2.host_id