SQL函数 IFNULL

SQL函数 IFNULL

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

大纲

代码语言：javascript复制

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，示例如下:

代码语言：javascript复制

/// 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()
}

代码语言：javascript复制

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的值可以指定为字符串，示例如下:

代码语言：javascript复制

/// 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()
}

代码语言：javascript复制

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:

代码语言：javascript复制

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

99  99

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

代码语言：javascript复制

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

NUll    33

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

代码语言：javascript复制

/// 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"
}

代码语言：javascript复制

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的值:

代码语言：javascript复制

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"
}

代码语言：javascript复制

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':

代码语言：javascript复制

SELECT sqlName,
IFNULL(FavoriteColors,'No Preference','Preference') AS ColorPref
FROM Sample.Person

云数据库MySQL sql 编程算法 serverless express

0 人点赞