COALESCE (SQL)
構文
COALESCE(expression,expression [,...])
概要
COALESCE 関数は式のリストを左から右に評価し、NULL でない最初の式の値を返します。すべての式が NULL と評価されると、NULL が返されます。
文字列は、先頭と末尾の空白が維持され、そのまま返されます。数値は、先頭と末尾のゼロが削除され、キャノニック形式で返されます。
NULL の処理の詳細は、"NULL および空文字列" を参照してください。
返り値のデータ型
数値以外の式 (文字列や日付など) の評価ではすべてが同じデータ型である必要があり、そのデータ型の値が返されます。互換性のないデータ型の式を指定すると、SQLCODE -378 エラーと、データ型の不一致を示すエラー・メッセージが返されます。CAST 関数を使用すると、expression を互換性のあるデータ型に変換できます。
数値式にはさまざまなデータ型を使用できます。複数のデータ型で数値式を指定した場合、考えられるすべての結果値との互換性が最も高く、データ型の優先順位が最も高い expression データ型で値が返されます。
リテラル値 (文字列、数値、または NULL) は、データ型 VARCHAR として扱われます。指定する式が 2 つだけの場合、リテラル値は数値式と互換性があります。最初の expression が数値式である場合はそのデータ型が返され、最初の expression がリテラル値である場合は VARCHAR データ型が返されます。
引数
expression
評価される一連の式。複数の式はコンマで区切ったリストで指定します。式リストでは、140 個までの式を指定できます。
NULL を処理する関数の比較
以下の表は、さまざまな SQL 比較関数を示します。論理比較テストが True (A は B と同じ) の場合、各関数は特定の値を返し、False (A は B と同じではない) の場合、別の値を返します。これらの関数により、NULL の論理比較を実行できます。実際の等値 (または非等値) 条件比較で NULL を指定することはできません。
SQL 関数 | 比較テスト | 返り値 |
---|---|---|
COALESCE(ex1,ex2,...) | 各引数で ex = NULL |
True の場合、次の ex 引数をテストすべての ex 引数が True (NULL) の場合、NULL を返す False の場合、ex を返す |
IFNULL(ex1,ex2) [2 つの引数形式] | ex1 = NULL |
True の場合、ex2 を返す False の場合、NULL を返す |
IFNULL(ex1,ex2) [3 つの引数形式] | ex1 = NULL |
True の場合、ex2 を返す False の場合、ex3 を返す |
{fn IFNULL(ex1,ex2)} | ex1 = NULL |
True の場合、ex2 を返す False の場合、ex1 を返す |
ISNULL(ex1,ex2) | ex1 = NULL |
True の場合、ex2 を返す False の場合、ex1 を返す |
NVL(ex1,ex2) | ex1 = NULL |
True の場合、ex2 を返す False の場合、ex1 を返す |
NULLIF(ex1,ex2) | ex1 = ex2 |
True の場合、NULL を返す False の場合、ex1 を返す |
例
以下の例は、一連の値を取り、NULL 以外の値を持つ最初の値 (d) を返します。ObjectScript の空文字列 ("") が、InterSystems SQL では NULL として扱われる点に注意してください。
SELECT COALESCE("","","","firstdata","","nextdata")
以下の例は、2 つの列の値を左から右に比較し、NULL でない最初の列の値を返します。FavoriteColors 列には NULL の行があり、Home_State 列は NULL になりません。COALESCE では、2 つを比較するために、FavoriteColors must を文字列としてキャストする必要があります。
SELECT TOP 25 Name,FavoriteColors,Home_State,
COALESCE(CAST(FavoriteColors AS VARCHAR),Home_State) AS CoalesceCol
FROM Sample.Person
以下の Dynamic SQL の例は、COALESCE を他の NULL を処理する関数と比較します。
SET myquery = "SELECT TOP 50 %ID,"_
"IFNULL(FavoriteColors,'blank') AS Ifn2Col,"_
"IFNULL(FavoriteColors,'blank','value') AS Ifn3Col,"_
"COALESCE(CAST(FavoriteColors AS VARCHAR),Home_State) AS CoalesceCol,"_
"ISNULL(FavoriteColors,'blank') AS IsnullCol,"_
"NULLIF(FavoriteColors,$LISTBUILD('Orange')) AS NullifCol,"_
"NVL(FavoriteColors,'blank') AS NvlCol"_
" FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"