文章目录
第二十九章 SQL函数 COALESCE
返回第一个非空表达式的值的函数。
大纲
COALESCE(expression,expression [,...])
-
expression - 要计算的一系列表达式。多个表达式被指定为逗号分隔的列表。此表达式列表限制为140个表达式。
描述
COALESSE函数按从左到右的顺序计算表达式列表，并返回第一个非空表达式的值。如果所有表达式的计算结果都为NULL，则返回NULL。
字符串返回时保持不变；保留前导和尾随空格。数字以规范形式返回，去掉了前导零和尾随零。
返回值的数据类型
非数字表达式(如字符串或日期)必须都属于相同的数据类型，并返回该数据类型的值。指定数据类型不兼容的表达式会导致SQLCODE-378错误，并显示DataType不匹配错误消息。可以使用CAST函数将表达式转换为兼容的数据类型。
数值表达式可以是不同的数据类型。如果指定具有不同数据类型的数值表达式，则返回的数据类型是与所有可能的结果值最兼容的表达式数据类型，即具有最高数据类型优先级的数据类型。
文字值(字符串、数字或NULL)被视为数据类型VARCHAR。如果只指定两个表达式，则文字值与数值表达式兼容：如果第一个表达式是数值表达式，则返回其数据类型；如果第一个表达式是文字值，则返回VARCHAR数据类型。
比较NULL处理函数
下表显示了各种SQL比较函数。如果逻辑比较测试为True(A与B相同)，则每个函数返回一个值；如果逻辑比较测试为False(A与B不同)，则每个函数返回另一个值。这些函数允许执行空逻辑比较。不能在实际相等(或不相等)条件比较中指定NULL。
SQL函数 | 比较测试 | 返回值 |
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 |
IFNULL(ex1,ex2) [two-argument form] | ex1 = NULL | True returns ex2 False returns NULL |
IFNULL(ex1,ex2) [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 |
示例
下面的嵌入式SQL示例接受一系列主机变量值，并返回第一个非空的(值d)。请注意，ObjectScript空字符串("")在 SQL中被转换为NULL：
ClassMethod Coalesce()
{
s (a, b, c, e) = ""
s d = "firstdata"
s f = "nextdata"
&sql(SELECT COALESCE(:a,:b,:c,:d,:e,:f) INTO :x)
if SQLCODE '= 0 {
w !,"Error code ",SQLCODE
} else {
w !,"The first non-null value is: ",x
}
}
DHC-APP>d ##class(PHA.TEST.SQLCommand).Coalesce()
The first non-null value is: firstdata
下面的示例按从左到右的顺序比较两列的值，并返回第一个非空列的值。对于某些行，FavoriteColors列为NULL；Home_State列从不为NULL。为了让Coalesce将两者进行比较，FavoriteColors必须转换为字符串：
SELECT TOP 25 Name,FavoriteColors,Home_State,
COALESCE(CAST(FavoriteColors AS VARCHAR),Home_State) AS CoalesceCol
FROM Sample.Person
以下动态SQL示例将COALESCE与其他NULL处理函数进行比较：
ClassMethod Coalesce1()
{
s 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"
s tStatement = ##class(%SQL.Statement).%New()
s qStatus = tStatement.%Prepare(myquery)
if qStatus '= 1 {
w "%Prepare failed:"
d $System.Status.DisplayError(qStatus)
q
}
s rset = tStatement.%Execute()
d rset.%Display()
w !,"End of data"
}
DHC-APP>d ##class(PHA.TEST.SQLCommand).Coalesce1()
ID Ifn2Col Ifn3Col CoalesceCol IsnullCol NullifCol NvlCol
1 value $lb("Red","Orange","Yellow") $lb("Red","Orange","Yellow") $lb("Red","Orange","Yellow") $lb("Red","Orange","Yellow")
2 blank blank blank blank
6 blank blank blank blank
9 blank blank blank blank
10 blank blank blank blank
13 value $lb("Red","Orange","Yellow","Green") $lb("Red","Orange","Yellow","Green") $lb("Red","Orange","Yellow","Green") $lb("Red","Orange","Yellow","Green")
14 value ReOrangYellowGreen $lb("Red","Orange","Yellow","Green","Green") $lb("Red","Orange","Yellow","Green","Green") $lb("Red","Orange","Yellow","Green","Green")
15 value ReOrangYellowGreen $lb("Red","Orange","Yellow","Green","Yellow") $lb("Red","Orange","Yellow","Green","Yellow") $lb("Red","Orange","Yellow","Green","Yellow")
16 value $lb("White") $lb("White") $lb("White") $lb("White")
17 value $lb("Black") $lb("Black") $lb("Black") $lb("Black")
18 value $lb("Green","White") $lb("Green","White") $lb("Green","White") $lb("Green","White")
19 value $lb("Purple") $lb("Purple") $lb("Purple") $lb("Purple")
20 value $lb("Yellow") $lb("Yellow") $lb("Yellow") $lb("Yellow")
21 value $lb("Red","Red") $lb("Red","Red") $lb("Red","Red") $lb("Red","Red")
22 value $lb("Black","Black") $lb("Black","Black") $lb("Black","Black") $lb("Black","Black")
23 blank blank MT blank blank
24 value $lb("Blue") $lb("Blue") $lb("Blue") $lb("Blue")
25 blank blank WY blank blank
26 value $lb("White") $lb("White") $lb("White") $lb("White")
27 value $lb("Orange") $lb("Orange") $lb("Orange")
28 value $lb("Orange","White") $lb("Orange","White") $lb("Orange","White") $lb("Orange","White")
29 value $lb("Black") $lb("Black") $lb("Black") $lb("Black")
30 value $lb("Red","Green") $lb("Red","Green") $lb("Red","Green") $lb("Red","Green")
31 value $lb("Purple") $lb("Purple") $lb("Purple") $lb("Purple")
32 value $lb("Green","Red") $lb("Green","Red") $lb("Green","Red") $lb("Green","Red")
33 blank blank TX blank blank
34 value $lb("Blue","Green") $lb("Blue","Green") $lb("Blue","Green") $lb("Blue","Green")
35 value $lb("Yellow") $lb("Yellow") $lb("Yellow") $lb("Yellow")
36 value $lb("Red","Blue") $lb("Red","Blue") $lb("Red","Blue") $lb("Red","Blue")
37 blank blank HI blank blank
38 blank blank OR blank blank
39 blank blank NM blank blank
40 blank blank NJ blank blank
41 blank blank NY blank blank
42 value $lb("Black","Orange") $lb("Black","Orange") $lb("Black","Orange") $lb("Black","Orange")
43 value $lb("Blue") $lb("Blue") $lb("Blue") $lb("Blue")
44 blank blank MO blank blank
45 value $lb("Purple","Yellow") $lb("Purple","Yellow") $lb("Purple","Yellow") $lb("Purple","Yellow")
46 value $lb("Orange") $lb("Orange") $lb("Orange")
47 value $lb("Yellow","Green") $lb("Yellow","Green") $lb("Yellow","Green") $lb("Yellow","Green")
48 value $lb("Blue","Blue") $lb("Blue","Blue") $lb("Blue","Blue") $lb("Blue","Blue")
49 value $lb("Blue") $lb("Blue") $lb("Blue") $lb("Blue")
50 value $lb("White","Red") $lb("White","Red") $lb("White","Red") $lb("White","Red")
51 value $lb("White","Green") $lb("White","Green") $lb("White","Green") $lb("White","Green")
52 blank blank MT blank blank
53 value $lb("Red") $lb("Red") $lb("Red") $lb("Red")
54 blank blank MD blank blank
55 value $lb("Orange","Orange") $lb("Orange","Orange") $lb("Orange","Orange") $lb("Orange","Orange")
56 blank blank MD blank blank
57 value $lb("White") $lb("White") $lb("White") $lb("White")
50 Rows(s) Affected
End of data
