CODEWITHSUNDEEP
sqlsql-servert-sqlcase
Chris
Chris

Reputation: 495

CASE with ORDER BY causes "Operand type clash: float is incompatible with date" T-SQL

Here's the 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

This gives an error on the following lines:

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

If I remove the CASE and just put in:

ORDER BY p.overall_rating ASC

I get the expected result:

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

What is it about having the CASE in there causes the error: "Operand type clash: float is incompatible with date" to come up? It works when I CAST the 4 fields as VARCHARs in the ORDER BY, but converting floats/ints to varchar does not bode well for sorting.

Upvotes: 1

Views: 587

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

Yup. You are using a case expression. It returns a single value -- and all paths must have the same type.

This problem occurs particularly with this type of order by expression. The solution is simple. Use multiple case expressions:

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,
         . . .

Upvotes: 2

Related Questions