Oracle SQL 関数 : COALESCE : 指定リストのNULL以外判定
numberRet := COALESCE( expr1, expr2, ... , exprN ); COALESCE 関数は指定された式リスト expr1...N を順に判定し NULL ではない式の値を返します。 式リストが全て NULL の場合は NULL を返します。
以下に COALESCE 関数の例を示します。
SQL> SET NULL '<NULL>' SQL> COL ALLNULL FORMAT A10 SQL> SELECT 2 COALESCE( NULL, NULL, '/TEST1/' ) AS "TEST1" 3 ,COALESCE( NULL, '/TEST2/', NULL, '/TEST22/' ) AS "TEST2" 4 ,COALESCE( NULL, NULL, NULL ) "ALLNULL" 5 FROM DUAL; TEST1 TEST2 ALLNULL -------------- -------------- ---------- /TEST1/ /TEST2/ <NULL>
上記の SET NULL コマンドはデータが NULL の場合に代替して表示する文字列を設定します。
また COL[UMN] コマンドは指定したカラム名の表示形式を指定します。
上記の場合は「ALLNULL」と言うカラム名の場合に半角文字数10桁で表示する設定です。
なお COALESCE の引数が1個しかない場合は以下の様にエラーが発生します。
SQL> SELECT 2 COALESCE( NULL ) AS "ALLNULL" 3 FROM DUAL; COALESCE( NULL ) AS "ALLNULL" * 行2でエラーが発生しました。: ORA-00938: 関数の引数が不足しています。
引き数の式リストはリテラル(直値)だけではなく、テーブルのカラムも指定できます。
以下は、テーブルを仮想的に「DUAL」表から4行の各種データを生成したものを COALESCE 関数で扱っています。
当然、リアルなテーブルのカラムでも指定可能です。
SQL> SELECT 2 NAME 3 ,COALESCE(VAL1, VAl2, VAL3) 4 FROM ( 5 SELECT 100 AS VAL1, 200 AS VAL2, 300 AS VAL3, '1st' AS NAME FROM DUAL 6 UNION 7 SELECT NULL AS VAL1, 2000 AS VAL2, 300 AS VAL3, '2nd' AS NAME FROM DUAL 8 UNION 9 SELECT NULL AS VAL1, NULL AS VAL2, 3000 AS VAL3, '3rd' AS NAME FROM DUAL 10 UNION 11 SELECT NULL AS VAL1, NULL AS VAL2, NULL AS VAL3, 'null' AS NAME FROM DUAL 12 ) TX; NAME COALESCE(VAL1,VAL2,VAL3) -------- ------------------------ 1st 100 2nd 2000 3rd 3000 null <NULL>
COALESCE 関数の使い道ですが、テーブル上の複数のカラムの中で
NULL ではないカラムのデータを対象としたい場合に使えそうです。