非数値(NaN)なら別の値に変換

syntax

NANVL ( float_expr1 , float_expr2 ) Oracle 10g
return [ binary_float | binary_double ]

parameter

float_expr1非数値を含む可能性のある浮動小数点式
float_expr2浮動小数点式

return

  • float_expr1 が 非数値(NaN) なら 式 float_expr2 の値を戻す
  • float_expr1 が 数値、または、無限大なら 式 float_expr1 の値を戻す

avail

  • SQL および PL/SQL の両方で使用可能。

NANVL 関数の内容

NANVL は、たぶん Non Available Number Value Logic の略

非数値を含む可能性のある浮動小数点式 float_expr1 が非数値(NaN: Non available Number)の場合に 浮動小数点式 float_expr2 に変換する。
浮動小数点 (BINARY_FLOAT、BINARY_DOUBLE) のみ有効な関数

注意

非数値をデータに含んでいる場合に集計(SUM)や平均(AVG)関数は計算できずに NaN を戻す。そのために Nan を NULL にするため(※) にあるかと思われる表記であったが、実際にテストして 浮動小数点式 float_expr2 に NULL を指定すると戻り値はすべて NULL になってしまう。

(※) 特に AVG 関数は NULL 項目を計算から除外するので 0 に置換した場合と NULL に置換した場合では結果が異なり、マニュアルにも、それをうかがわせる説明もあるので NULL がサポートされるように変更されることを期待する。

NANVL 使用例

SQL> set null '<NULL>'
SQL> select d, num, NANVL(d, 0), NANVL(d, null) from nanvl_sample
 
         D        NUM NANVL(D,0) NANVL(D,NULL)
---------- ---------- ---------- -------------
  3.0E+000          3   3.0E+000 <NULL>
  4.0E+000          4   4.0E+000 <NULL>
  5.0E+000          5   5.0E+000 <NULL>
       Nan <NULL>              0 <NULL>
       Inf <NULL>            Inf <NULL>
      -Inf <NULL>           -Inf <NULL>

SQL*Plus の NULL 表示

プラス無限大、マイナス無限大、非数値を含む演算を行なうと符号などによって結果が NaN になる。

SQL> select count(*),sum(num), avg(num),
  2         sum(d), avg(d) from nanvl_sample;
 
  COUNT(*)   SUM(NUM)   AVG(NUM)     SUM(D)     AVG(D)
---------- ---------- ---------- ---------- ----------
         6         12          4        Nan        Nan
                                       ^^^        ^^^

上記のデータから 非数値、無限大を取り除いて合計と平均を求める。

SQL> select count(*),sum(num), avg(num),
  2         sum(NANVL(d,0)), avg(NANVL(d,0))
  3   from nanvl_sample where d not in ('NaN', '+INF', '-INF');
 
  COUNT(*)   SUM(NUM)   AVG(NUM) SUM(NANVL(D,0)) AVG(NANVL(D,0))
---------- ---------- ---------- --------------- ---------------
         3         12          4        1.2E+001        4.0E+000
       ^^^             ^^^^^^^^^                        ^^^^^^^^

データから正と負の無限大を取り除き「非数値を 0 」にて変換し合計と平均を求める。

SQL> select count(*),sum(num), avg(num),
  2         sum(NANVL(d,0)), avg(NANVL(d,0))
  3   from nanvl_sample where d not in ('+INF', '-INF');
 
  COUNT(*)   SUM(NUM)   AVG(NUM) SUM(NANVL(D,0)) AVG(NANVL(D,0))
---------- ---------- ---------- --------------- ---------------
         4         12          4        1.2E+001        3.0E+000
       ^^^              ^^^^^^^^                        ^^^^^^^^
                NULL 値のため除外           0 に変換したため 3.0


SQL 関数 - NANVL 関連

 
アルファベット別 関数一覧 ショートカット
ABCDEFG
HILMNOP
RSTUVWX
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ 会員制(無料)の公式技術サイト