概要
COALESCE 関数は式のリストを左から右に評価し、NULL でない最初の式の値を返します。すべての式が NULL と評価されると、NULL が返されます。
文字列は、先頭と末尾の空白が維持され、そのまま返されます。数値は、先頭と末尾のゼロが削除され、キャノニック形式で返されます。
NULL の処理方法の詳細は、"InterSystems SQL の使用法" の “言語要素” にある "NULL および空文字列" のセクションを参照してください。
返り値のデータ型
数値以外の式 (文字列や日付など) の評価ではすべてが同じデータ型である必要があり、そのデータ型の値が返されます。互換性のないデータ型の式を指定すると、SQLCODE -378 エラーと、データ型の不一致を示すエラー・メッセージが返されます。CAST 関数を使用すると、expression を互換性のあるデータ型に変換できます。
数値式にはさまざまなデータ型を使用できます。複数のデータ型で数値式を指定した場合、考えられるすべての結果値との互換性が最も高く、データ型の優先順位が最も高い expression データ型で値が返されます。
リテラル値 (文字列、数値、または NULL) は、データ型 VARCHAR として扱われます。指定する式が 2 つだけの場合、リテラル値は数値式と互換性があります。最初の expression が数値式である場合はそのデータ型が返され、最初の expression がリテラル値である場合は VARCHAR データ型が返されます。
例
以下の埋め込み SQL の例は、一連のホスト変数値を取り、NULL 以外の値を持つ最初の値 (d) を返します。ObjectScript の空文字列 ("") が、InterSystems 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 を処理する関数と比較します。
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"