时间戳

首页 > 解决方案 > 带有 ORDER BY 的 CASE 导致“操作数类型冲突：float 与日期不兼容”T-SQL

sql - 带有 ORDER BY 的 CASE 导致“操作数类型冲突：float 与日期不兼容”T-SQL

问题描述

这是SQL：

DECLARE @facOrGroup varchar(max) = '2,3,4,5,6',
    @sDate date = '11/01/2017',
    @eDate date = '11/01/2018',
    @orderBy varchar(40) = 'Facility Code',
    @orderDir varchar(4) = 'asc'

SELECT
    f.facCode as [Facility Code]
    ,f.facName as Facility
    ,convert(varchar(10),p.[period],101) as [Period]
    ,p.[provnum] as [Medicare CCN]
    ,p.overall_rating as [Overall Rating]
    ,p.quality_rating as [Inspection Rating]
    ,p.staffing_rating as [Staffing Rating]
    ,p.rn_staffing_rating as [RN Staffing Rating]
FROM Director.dbo.cmsProviderInfo p
INNER JOIN dbo.facInfo f
    ON
        f.medicareCCN=p.provNum
WHERE
    f.facID in (SELECT * FROM dbo.fn_SplitCSV(@facOrGroup)) AND
    period between @sDate and @eDate
ORDER BY
    CASE @orderDir WHEN 'asc' THEN
        CASE @orderBy
            WHEN 'Facility Code' THEN f.facCode
            WHEN 'Facility' THEN f.facName
            WHEN 'Period' THEN p.[period]
            WHEN 'Medicare CCN' THEN p.[provnum]
            WHEN 'Overall Rating' THEN p.overall_rating
            WHEN 'Inspection Rating' THEN p.quality_rating
            WHEN 'Staffing Rating' THEN p.staffing_rating
            WHEN 'RN Staffing Rating' THEN p.rn_staffing_rating
        END
    END ASC,
    CASE @orderDir WHEN 'desc' THEN
        CASE @orderBy
            WHEN 'Facility Code' THEN f.facCode
            WHEN 'Facility' THEN f.facName
            WHEN 'Period' THEN p.[period]
            WHEN 'Medicare CCN' THEN p.[provnum]
            WHEN 'Overall Rating' THEN p.overall_rating
            WHEN 'Inspection Rating' THEN p.quality_rating
            WHEN 'Staffing Rating' THEN p.staffing_rating
            WHEN 'RN Staffing Rating' THEN p.rn_staffing_rating
        END
    END DESC

GO

这会在以下几行中产生错误：

WHEN 'Overall Rating' THEN p.overall_rating
WHEN 'Inspection Rating' THEN p.quality_rating
WHEN 'Staffing Rating' THEN p.staffing_rating
WHEN 'RN Staffing Rating' THEN p.rn_staffing_rating

如果我删除 CASE 并放入：

ORDER BY p.overall_rating ASC

我得到了预期的结果：

FAC1    Facility1    02/01/2018    455895    2    4    1    1
FAC1    Facility1    06/01/2018    455895    2    4    1    2
FAC1    Facility1    12/01/2017    455895    2    4    1    2
FAC1    Facility1    01/01/2018    455895    2    4    1    1
FAC1    Facility1    03/01/2018    455895    2    4    1    2
FAC1    Facility1    11/01/2017    455895    3    4    1    2
FAC1    Facility1    09/01/2018    455895    3    4    2    2

将 CASE 放在那里会导致错误：“操作数类型冲突：float 与日期不兼容”出现是什么原因？当我在 ORDER BY 中将 4 个字段转换为 VARCHAR 时，它可以工作，但是将浮点数/整数转换为 varchar 对排序来说并不是一个好兆头。

标签： sqlsql-servertsqlcase

解决方案

是的。您正在使用case表达式。它返回一个值——所有路径必须具有相同的类型。

尤其是这种类型的order by表达式会出现此问题。解决方案很简单。使用多个case表达式：

ORDER BY (CASE WHEN @orderDir = 'asc' AND @orderBy =  'Facility Code' THEN f.facCode END),
         (CASE WHEN @orderDir = 'asc' AND @orderBy =  'Facility' THEN f.facName END),
         . . .
         (CASE WHEN @orderDir = 'desc' AND @orderBy =  'Facility Code' THEN f.facCode END) DESC,
         (CASE WHEN @orderDir = 'desc' AND @orderBy =  'Facility' THEN f.facName END) DESC,
         . . .

推荐阅读