IFNULL(expression-1,expression-2 [,expression-3])
{fn IFNULL(expression-1,expression-2)}
概要
InterSystems IRIS は IFNULL を SQL 汎用関数と ODBC スカラ関数の両方としてサポートしています。双方は大変よく似た処理を実行しますが、機能上は異なります。SQL 汎用関数は、3 つの引数をサポートします。ODBC スカラ関数は、2 つの引数をサポートします。2 つの引数の SQL 汎用関数と ODBC スカラ関数の機能は同じではありません。expression-1 が NULL でない場合に異なる値を返します。
SQL 汎用関数は、expression-1 が NULL かどうかを評価します。expression-1 を返すことはありません。
-
expression-1 が NULL の場合は、expression-2 を返します。
-
expression-1 が NULL でない場合は、expression-3 を返します。
-
expression-1 が NULL でなく、expression-3 が指定されていない場合は、NULL を返します。
ODBC スカラ関数は、expression-1 が NULL かどうかを評価します。これは expression-1 または expression-2 のいずれかを返します。
NULL の処理方法の詳細は、"InterSystems SQL の使用法" の “言語要素” の章にある "NULL" のセクションを参照してください。
返り値のデータ型
-
IFNULL(expression-1,expression-2) : expression-2 のデータ型を返します。expression-2 が数値リテラル、文字列リテラル、または NULL である場合、データ型 VARCHAR を返します。
-
IFNULL(expression-1,expression-2,expression-3) : expression-2 と expression-3 が異なるデータ型を持つ場合、より高い (より包括的な) データ型の優先順位を持つデータ型を返します。expression-2 または expression-3 が数値リテラルまたは文字列リテラルである場合、データ型 VARCHAR を返します。expression-2 または expression-3 が NULL である場合、非 NULL 引数のデータ型を返します。
expression-2 と expression-3 が異なる長さ、精度、または小数桁を持つ場合、IFNULL は2 つの式のうち、より大きい長さ、精度、または小数桁を返します。
-
{fn IFNULL(expression-1,expression-2)} : expression-1 のデータ型を返します。expression-1 が数値リテラル、文字列リテラル、または NULL である場合、データ型 VARCHAR を返します。
DATE および TIME の表示変換
一部の expression-1 データ型では、論理モード (モード 0) から ODBC モード (モード 1) または表示モード (モード 2) への変換が必要です。例えば、DATE データ型と TIME データ型です。expression-2 または expression-3 の値が同じデータ型でない場合は、この値を ODBC モードまたは表示モードに変換することはできません。DATE データ型の場合は SQLCODE エラー -146、TIME データ型の場合は SQLCODE エラー -147 が生成されます。例えば、IFNULL(DOB,'nodate',DOB) は、ODBC モードまたは表示モードでは実行できません。SQLCODE -146 エラーが発行され、%msg が "エラー : 'nodate' は無効な ODBC/JDBC 日付値です" または "エラー : 'nodate' は無効な表示日付値です" に設定されます。ODBC モードまたは表示モードでこの文を実行するには、値を適切なデータ型 IFNULL(DOB,CAST('nodate' as DATE),DOB) としてキャストする必要があります。これにより日付 0 になり、1840-12-31 として表示されます。
%List の表示変換
%List フィールドは、エンコーディングが設定された文字列データ型です。expression-1 が %List フィールドである場合、expression-2 または expression-3 の適切な値は、選択モードによって異なります。
-
論理モード (モード 0) または表示モード (モード 2) の場合、%List の値は、$lb("element1","element2",...) という形式の文字列データ型として返されます。したがって、以下の例に示すように、expression-2 または expression-3 の値は %List として指定する必要があります。
SET myquery=3
SET myquery(1)="SELECT TOP 20 Name,"
SET myquery(2)="IFNULL(FavoriteColors,$LISTBUILD('No Preference'),FavoriteColors) AS ColorChoice "
SET myquery(3)="FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement).%New(2) // 2=Display mode
SET qStatus = tStatement.%Prepare(.myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
-
ODBC モード (モード 1) では、%List の値は、element1,element2,... というコンマ区切り要素の文字列として返されます。したがって、以下の例に示すように、expression-2 または expression-3 の値は文字列として指定できます。
SET myquery=3
SET myquery(1)="SELECT TOP 20 Name,"
SET myquery(2)="IFNULL(FavoriteColors'No Preference',FavoriteColors) AS ColorChoice "
SET myquery(3)="FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement).%New(1) // 1=ODBC mode
SET qStatus = tStatement.%Prepare(.myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
例
以下の例では、汎用関数と ODBC スカラ関数は、最初の式が NULL なので、どちらも 2 番目の式 (99) を返します。
SELECT IFNULL(NULL,99) AS NullGen,{fn IFNULL(NULL,99)} AS NullODBC
以下の例では、汎用関数と ODBC スカラ関数は、異なる値を返します。汎用関数は、最初の式が NULL ではないので、<null> を返します。ODBC の例は、最初の式が NULL ではないので、最初の式 (33) を返します。
SELECT IFNULL(33,99) AS NullGen,{fn IFNULL(33,99)} AS NullODBC
以下の動的 SQL の例は、FavoriteColors が NULL の場合は文字列 'No Preference' を返し、そうでない場合は NULL を返します。
SET myquery=3
SET myquery(1)="SELECT Name,"
SET myquery(2)="IFNULL(FavoriteColors,'No Preference') AS ColorChoice "
SET myquery(3)="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"
以下の動的 SQL の例は、FavoriteColors が NULL の場合は文字列 'No Preference' を返し、そうでない場合は FavoriteColors の値を返します。
SET myquery=3
SET myquery(1)="SELECT Name,"
SET myquery(2)="IFNULL(FavoriteColors,'No Preference',FavoriteColors) AS ColorChoice "
SET myquery(3)="FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=2
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"
以下の例は、FavoriteColors が NULL の場合は文字列 'No Preference' を返し、そうでない場合は文字列 'Preference' を返します。
SELECT Name,
IFNULL(FavoriteColors,'No Preference','Preference') AS ColorPref
FROM Sample.Person
以下の ODBC 構文の例は、FavoriteColors が NULL の場合は文字列 'No Preference' を返し、そうでない場合は FavoriteColors データ値を返します。
SELECT Name,
{fn IFNULL(FavoriteColors,$LISTBUILD('No Preference'))} AS ColorPref
FROM Sample.Person
SET myquery=3
SET myquery(1)="SELECT Name,"
SET myquery(2)="{fn IFNULL(FavoriteColors,'No Preference')} AS ColorChoice "
SET myquery(3)="FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
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"
以下の例では、WHERE 節で IFNULL を使用しています。これは、色の好みがない、21 歳未満の人を選択します。FavoriteColors が NULL の場合、IFNULL は Age フィールドの値を返し、その値が条件テストに使用されます。
SELECT Name,FavoriteColors,Age
FROM Sample.Person
WHERE 21 > IFNULL(FavoriteColors,Age)
ORDER BY Age
類似の機能については、NULL 述語 (IS NULL、IS NOT NULL) を参照してください。