COALESCE
Synopsis
COALESCE(expression,expression)
引数
expression | 評価される一連の式。複数の式はコンマで区切ったリストで指定します。式リストでは、140 個までの式を指定できます。 |
概要
COALESCE 関数は式のリストを左から右に評価し、NULL でない最初の式の値を返します。すべての式が NULL と評価されると、NULL が返されます。
数値以外の式 (文字列や日付など) の評価ではすべてが同じデータ型である必要があり、そのデータ型の値が返されます。互換性のないデータ型の式を指定すると、SQLCODE -378 と %msg エラー・メッセージ値が返されます。CAST 関数を使用すると、expression を互換性のあるデータ型に変換できます。
数値式にはさまざまなデータ型を使用できます。複数のデータ型で数値式を指定した場合、考えられるすべての結果値との互換性が最も高く、データ型の優先順位が最も高い expression データ型で値が返されます。DOUBLE、NUMERIC、BIGINT、INTEGER、SMALLINT、TINYINT の各データ型に互換性があり、この優先度 (最高レベルから最低レベル) で順位が指定されます。
文字列は、先頭と末尾の空白が維持され、そのまま返されます。数値は、先頭と末尾のゼロが削除され、キャノニック形式で返されます。
NULL の処理方法の詳細は、"Caché SQL の使用法" の “言語要素” にある "NULL および空文字列" のセクションを参照してください。
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 を返す |
例
以下の埋め込み SQL の例は、一連のホスト変数値を取り、NULL 以外の値を持つ最初の値 (d) を返します。ObjectScript の空文字列 ("") が、Caché SQL では NULL として扱われる点に注意してください。
SET (a,b,c,e)=""
SET d="firstdata"
SET f="nextdata"
&sql(SELECT COALESCE(:a,:b,:c,:d,:e,:f) INTO :x)
IF SQLCODE'=0 {
WRITE !,"Error code ",SQLCODE }
ELSE {
WRITE !,"The first non-null value is: ",x }
以下の例は、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 を処理する関数と比較します。
ZNSPACE "SAMPLES"
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"