oracle nvl()函数在使用中出现的问题

---

看一条sql

select q.*, r.goods_name
  from (select nvl(t.goods_code, s.goods_code) goods_code,
               t.buy_open_price,
               t.buy_position_price,
               t.buy_position_amount,
               s.sale_open_price,
               s.sale_position_price,
               s.sale_position_amount,
               (nvl(t.buy_position_amount, 0) -
               nvl(s.sale_position_amount, 0)) position_amount
          from (select opo.goods_code,
                       opo.position_bs,
                       sum(opo.open_price * opo.position_amount) /
                       sum(opo.position_amount) buy_open_price,
                       sum(opo.position_price * opo.position_amount) /
                       sum(opo.position_amount) buy_position_price,
                       sum(opo.position_amount) buy_position_amount
                  from otc_trade_position_open opo
                 where opo.position_bs = '1'
                   and opo.account_type = '1'
                   and position_bs = '2'
                 group by opo.goods_code, opo.position_bs) t
          full outer join (select opo.goods_code,
                                 opo.position_bs,
                                 sum(opo.open_price * opo.position_amount) /
                                 sum(opo.position_amount) sale_open_price,
                                 sum(opo.position_price *
                                     opo.position_amount) /
                                 sum(opo.position_amount) sale_position_price,
                                 sum(opo.position_amount) sale_position_amount
                            from otc_trade_position_open opo
                           where opo.position_bs = '2'
                             and opo.account_type = '1'
                             and position_bs = '2'
                           group by opo.goods_code, opo.position_bs) s
            on (t.goods_code = s.goods_code)) q
  left outer join otc_goods r
    on (q.goods_code = r.goods_code)

---

 

结果如下

 

 

---

 

再看下一个sql

select q.*, r.goods_name
  from (select nvl(t.goods_code, s.goods_code) goods_code,
               t.buy_open_price,
               t.buy_position_price,
               t.buy_position_amount,
               s.sale_open_price,
               s.sale_position_price,
               s.sale_position_amount,
               (nvl(t.buy_position_amount, 0) -
               nvl(s.sale_position_amount, 0)) position_amount
          from (select opo.goods_code,
                       opo.position_bs,
                       sum(opo.open_price * opo.position_amount) /
                       sum(opo.position_amount) buy_open_price,
                       sum(opo.position_price * opo.position_amount) /
                       sum(opo.position_amount) buy_position_price,
                       sum(opo.position_amount) buy_position_amount
                  from otc_trade_position_open opo
                 where opo.position_bs = '1'
                   and opo.account_type = '1'
                   and position_bs = '1'
                 group by opo.goods_code, opo.position_bs) t
          full outer join (select opo.goods_code,
                                 opo.position_bs,
                                 sum(opo.open_price * opo.position_amount) /
                                 sum(opo.position_amount) sale_open_price,
                                 sum(opo.position_price *
                                     opo.position_amount) /
                                 sum(opo.position_amount) sale_position_price,
                                 sum(opo.position_amount) sale_position_amount
                            from otc_trade_position_open opo
                           where opo.position_bs = '2'
                             and opo.account_type = '1'
                             and position_bs = '1'
                           group by opo.goods_code, opo.position_bs) s
            on (t.goods_code = s.goods_code)) q
  left outer join otc_goods r
    on (q.goods_code = r.goods_code)

 

---

 

查询方法一样，就是条件变了一下，可是联合查询的商品名称却在上面一个里面查询不出来，而下面一个里面显示正常。分析原因，nvl函数的问题，nvl(t.goods_code, s.goods_code) 前面一个查询语句里面，goods_code是取的s.goods_code,后面一个取的是t.goods_code.虽然值一样，但是前面一个联合查询却查询不出来，有点诡异。

---

 

解决方法：换成nvl2, 将上面代码里面的 nvl(t.goods_code,s.goods_code)换成nvl2(t.goods_code, t.goods_code, s.goods_code)上面存在的问题即可解决。