第二十九章 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