第六十三章 SQL函数 IFNULL

测试NULL并返回适当表达式的函数。

大纲

IFNULL(expression-1,expression-2 [,expression-3])

{fn IFNULL(expression-1,expression-2)}

参数

• expression-1 - 要计算以确定是否为NULL的表达式。
• expression-2 - 如果expression-1为NULL，则返回的表达式。
• expression-3 - 可选-如果expression-1不是NULL返回的表达式。
  如果没有指定expression-3，则当expression-1不是NULL时返回NULL值。

返回的数据类型描述如下。

描述

支持IFNULL作为SQL通用函数和ODBC标量函数。
请注意，虽然这两个执行非常相似的操作，但它们在功能上是不同的。
SQL通用函数支持三个参数。
ODBC标量函数支持两个参数。
SQL通用函数和ODBC标量函数的双参数形式是不一样的;
当expression-1不为空时，它们返回不同的值。

SQL通用函数计算表达式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:
- 如果expression-1为NULL，则返回expression-2。
- 如果expression-1不为NULL，则返回expression-1。

返回值数据类型

• 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返回两个表达式的更大长度、精度或比例。

• {fn IFNULL(expression-1,expression-2)}:返回expression-1的数据类型。
  如果expression-1是数字字面值、字符串字面值或NULL，则返回数据类型VARCHAR。

日期和时间显示转换

一些expression-1数据类型需要从逻辑模式(模式0)转换为ODBC模式(模式1)或显示模式(模式2)。例如DATE和TIME数据类型。
如果expression-2或expression-3的值不是相同的数据类型，则不能在ODBC模式或Display模式下转换该值，并产生一个SQLCODE错误:DATE数据类型为-146;
-147为TIME数据类型。
例如，IFNULL(DOB，'nodate'，DOB)不能在ODBC模式或显示模式中执行;
它会发出一个SQLCODE -146错误，其中有%msg Error: 'nodate' is an invalid ODBC/JDBC Date value or Error: 'nodate' is an invalid DISPLAY Date value.
要在ODBC模式或Display模式下执行此语句，必须将该值转换为适当的数据类型: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，示例如下:

/// d ##class(PHA.TEST.SQLCommand).IfNull()
ClassMethod IfNull()
{
    s myquery=3
    s myquery(1)="SELECT TOP 20 Name,"
    s myquery(2)="IFNULL(FavoriteColors,$LISTBUILD('No Preference'),FavoriteColors) AS ColorChoice "
    s myquery(3)="FROM Sample.Person"
    s tStatement = ##class(%SQL.Statement).%New(2)  // 2=Display mode
    s qStatus = tStatement.%Prepare(.myquery)
    s rset = tStatement.%Execute()
    d rset.%Display()
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).IfNull()
Name    ColorChoice
yaoxin  $lb("Red","Orange","Yellow")
xiaoli  $lb("No Preference")
姚鑫    $lb("No Preference")
姚鑫    $lb("No Preference")
姚鑫    $lb("No Preference")
姚鑫    $lb("Red","Orange","Yellow","Green")
姚鑫    $lb("Red","Orange","Yellow","Green","Green")
Isaacs,Roberta Z.       $lb("Red","Orange","Yellow","Green","Yellow")
Chadwick,Zelda S.Chadwick,Zelda S.Chadwick,Zelda S.Chadwick,Zelda S.Chadwick,Zelda S.   $lb("White")
Fives,James D.  $lb("Black")
Vonnegut,Jose P.        $lb("Green","White")
Chadbourne,Barb B.      $lb("Purple")
Quigley,Barb A. $lb("Yellow")
O'Rielly,Chris H.       $lb("Red","Red")
Willeke,Alvin L.        $lb("Black","Black")
Orwell,John V.  $lb("No Preference")
Umansky,Susan C.        $lb("Blue")
Kratzmann,Kirsten C.    $lb("No Preference")
Ng,Josephine Z. $lb("White")
Zevon,Heloisa O.        $lb("Orange")

20 Rows(s) Affected

• 在ODBC模式(模式1)中，%List值作为逗号分隔的元素字符串返回:element1、element2、....
  因此，expression-2或expression-3的值可以指定为字符串，示例如下:

/// d ##class(PHA.TEST.SQLCommand).IfNull1()
ClassMethod IfNull1()
{
    s myquery=3
    s myquery(1)="SELECT TOP 20 Name,"
    s myquery(2)="IFNULL(FavoriteColors,'No Preference',FavoriteColors) AS ColorChoice "
    s myquery(3)="FROM Sample.Person"
    s tStatement = ##class(%SQL.Statement).%New(1)  // 1=ODBC mode
    s qStatus = tStatement.%Prepare(.myquery)
    s rset = tStatement.%Execute()
    d rset.%Display()
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).IfNull1()
Name    ColorChoice
yaoxin  $lb("Red","Orange","Yellow")
xiaoli  No Preference
姚鑫    No Preference
姚鑫    No Preference
姚鑫    No Preference
姚鑫    $lb("Red","Orange","Yellow","Green")
姚鑫    $lb("Red","Orange","Yellow","Green","Green")
Isaacs,Roberta Z.       $lb("Red","Orange","Yellow","Green","Yellow")
Chadwick,Zelda S.Chadwick,Zelda S.Chadwick,Zelda S.Chadwick,Zelda S.Chadwick,Zelda S.   $lb("White")
Fives,James D.  $lb("Black")
Vonnegut,Jose P.        $lb("Green","White")
Chadbourne,Barb B.      $lb("Purple")
Quigley,Barb A. $lb("Yellow")

NULL处理函数比较

下表显示了各种SQL比较函数。
如果逻辑比较测试为True (A与B相同)，则每个函数返回一个值;如果逻辑比较测试为False (A与B不同)，则返回另一个值。这些函数允许执行NULL逻辑比较。
不能在实际相等(或不相等)条件比较中指定NULL。

示例

在下面的例子中，通用函数和ODBC标量函数都返回第二个表达式(99)，因为第一个表达式是NULL:

SELECT IFNULL(NULL,99) AS NullGen,{fn IFNULL(NULL,99)} AS NullODBC

99  99

在下面的示例中，通用函数和ODBC标量函数示例返回不同的值。
通用函数返回<null>，因为第一个表达式不是null。
ODBC示例返回第一个表达式(33)，因为第一个表达式不是NULL:

SELECT IFNULL(33,99) AS NullGen,{fn IFNULL(33,99)} AS NullODBC

NUll    33

下面的动态SQL示例返回字符串'No Preference'，如果FavoriteColors是NULL;
否则，返回NULL:

/// d ##class(PHA.TEST.SQLCommand).IfNull2()
ClassMethod IfNull2()
{
    s myquery=3
    s myquery(1)="SELECT Name,"
    s myquery(2)="IFNULL(FavoriteColors,'No Preference') AS ColorChoice "
    s myquery(3)="FROM Sample.Person"
    s tStatement = ##class(%SQL.Statement).%New()
    s qStatus = tStatement.%Prepare(.myquery)
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"End of data"
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).IfNull2()
Name    ColorChoice
yaoxin
xiaoli  No Preference
姚鑫    No Preference
姚鑫    No Preference
姚鑫    No Preference
姚鑫
姚鑫
Isaacs,Roberta Z.

下面的动态SQL示例返回字符串'No Preference'，如果FavoriteColors是NULL;
否则，它返回FavoriteColors的值:

ClassMethod IfNull3()
{
    s myquery=3
    s myquery(1)="SELECT Name,"
    s myquery(2)="IFNULL(FavoriteColors,'No Preference',FavoriteColors) AS ColorChoice "
    s myquery(3)="FROM Sample.Person"
    s tStatement = ##class(%SQL.Statement).%New()
    s tStatement.%SelectMode=2
    s qStatus = tStatement.%Prepare(.myquery)
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"End of data"
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).IfNull3()
Name    ColorChoice
yaoxin  $lb("Red","Orange","Yellow")
xiaoli  No Preference
姚鑫    No Preference
姚鑫    No Preference
姚鑫    No Preference
姚鑫    $lb("Red","Orange","Yellow","Green")
姚鑫    $lb("Red","Orange","Yellow","Green","Green")

下面的例子返回字符串'No Preference'如果FavoriteColors是NULL;
否则，返回字符串'Preference':

SELECT sqlName,
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

/// d ##class(PHA.TEST.SQLCommand).IfNull4()
ClassMethod IfNull4()
{
    s myquery=3
    s myquery(1)="SELECT Name,"
    s myquery(2)="{fn IFNULL(FavoriteColors,'No Preference')} AS ColorChoice "
    s myquery(3)="FROM Sample.Person"
    s tStatement = ##class(%SQL.Statement).%New()
    s tStatement.%SelectMode=1
    s qStatus = tStatement.%Prepare(.myquery)
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"End of data"
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).IfNull4()
Name    ColorChoice
yaoxin  Red,Orange,Yellow
xiaoli  No Preference
姚鑫    No Preference
姚鑫    No Preference
姚鑫    No Preference
姚鑫    Red,Orange,Yellow,Green
姚鑫    Red,Orange,Yellow,Green,Green
Isaacs,Roberta Z.       Red,Orange,Yellow,Green,Yellow

下面的例子在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)。