文章
· 二月 23, 2022 阅读大约需 8 分钟

第六十三章 SQL函数 IFNULL

第六十三章 SQL函数 IFNULL

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

大纲

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

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

参数

  • expression-1 - 要计算以确定是否为NULL的表达式。
  • expression-2 - 如果expression-1NULL,则返回的表达式。
  • 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-1NULL,则返回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-2expression-3具有不同的数据类型,则返回数据类型优先级更高(包容性更强)的数据类型。
    如果expression-2expression-3是数值字面值或字符串字面值,则返回数据类型VARCHAR
    如果expression-2expression-3NULL,则返回非NULL参数的数据类型。

如果expression-2expression-3的长度、精度或比例不同,则IFNULL返回两个表达式的更大长度、精度或比例。

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

日期和时间显示转换

一些expression-1数据类型需要从逻辑模式(模式0)转换为ODBC模式(模式1)或显示模式(模式2)。例如DATE和TIME数据类型。
如果expression-2expression-3的值不是相同的数据类型,则不能在ODBC模式或Display模式下转换该值,并产生一个SQLCODE错误:DATE数据类型为-146;
-147TIME数据类型。
例如,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-2Expression-3值取决于选择模式:

  • 在逻辑模式(模式0)或显示模式(模式2)中,%List值作为字符串数据类型返回,格式为$lb("element1","element2",…)
    因此,expression-2expression-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值作为逗号分隔的元素字符串返回:element1element2、....
    因此,expression-2expression-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

SQL Function Comparison Test Return Value
IFNULL(ex1,ex2) [two-argument form] ex1 = NULL True returns ex2 False returns NULL
IFNULL(ex1,ex2,ex3) [three-argument form] ex1 = NULL True returns ex2 False returns ex3
{fn IFNULL(ex1,ex2)} ex1 = NULL True returns ex2 False returns ex1
ISNULL(ex1,ex2) ex1 = NULL True returns ex2 False returns ex1
NVL(ex1,ex2) ex1 = NULL True returns ex2 False returns ex1
NULLIF(ex1,ex2) ex1 = ex2 True returns NULL False returns ex1
COALESCE(ex1,ex2,...) ex = NULL for each argument True tests next ex argument. If all ex arguments are True (NULL), returns NULL. False returns ex

示例

在下面的例子中,通用函数和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',如果FavoriteColorsNULL;
否则,返回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',如果FavoriteColorsNULL;
否则,它返回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'如果FavoriteColorsNULL;
否则,返回字符串'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岁以下没有最喜欢的颜色偏好的人。
如果FavoriteColorsNULL, IFNULL返回Age字段值,用于条件测试:

SELECT Name,FavoriteColors,Age
FROM Sample.Person
WHERE 21 > IFNULL(FavoriteColors,Age)
ORDER BY Age

类似的功能可以参考NULL谓词(IS NULL, IS NOT NULL)。

讨论 (0)1
登录或注册以继续