Understanding a Sql Server Query - CASE within an

问题:

I'm trying to mess around with putting CASE statement in the ORDER BY clause using a DISTINCT in the SELECT list, and finding some odd behavior that I don't understand. Here is some code:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
when 'b' = 'c' then BU
else ISO_ID
end

This works. But if I change the 4th line to when 'b' = 'b' then BU:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
when 'b' = 'b' then BU
else ISO_ID
end

it breaks with error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

When BU is obviously in the select list. Even stranger is when I change the code to:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
when 'b' = 'b' then BU
else BU   --change is here
end

It works again! How does that even make sense? Can someone help me wrap my brain around this one?

回答1:

The rules for CASE are that the result should be cast to the datatype of the branch with highest precedence.

For the first query it uses contradiction detection and just generates a plan that sorts by ISO_ID directly. This is numeric already so no need to implicitly cast and so matches the expression in the select list with no problem.

For the second query it can again determine at compile time that it needs to ORDER BY BU. Except it actually needs to ORDER BY CAST(BU AS NUMERIC) due to the above. This means it would need to ORDER BY a computed expression not matching anything anything in the SELECT list. Hence the problem.

Your third query removes the expression of higher precedence from the CASE thus removing the need for an implicit cast (and hence the need to order by a computed expression).

As the computed expression is entirely dependant upon the columns in the SELECT DISTINCT list however you could rewrite the second query as follows.

;WITH CTE AS
(
SELECT DISTINCT Requester,
                ISO_ID              AS ISO,
                ( ISO_ID - 5 + 50 ) AS 'someNum',
                BU
FROM   LoanerHeader
)
SELECT *
FROM CTE
ORDER  BY CASE
            WHEN 'a' = 'b' THEN Requester
            WHEN 'b' = 'b' THEN BU
            ELSE ISO
          END

回答2:

You must include the complete CASE expression in the SELECT list, not just the component parts.

The erratic behaviour that we observe here might occur if the data types of Requester, BU and ISO_ID are different. It is apparent from the example that ISO_ID is numeric. My guess from the behaviour is that neither Requester nor BU is numeric, but they are both of the same type (or compatible types). If they are both character types, then it might be necessary to cast ISO_ID to a character type in the CASE expression.

回答3:

I ran something like yours in SQL Server 2000 and it failed every time, when I went to SQL Server 2005, I started seeing your issue. I changed the code to look like:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU, 
CASE WHEN 'a' = 'b' then Requester
     when 'b' = 'c' then BU
     else ISO_ID
end AS SortByField
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
     when 'b' = 'c' then BU
     else ISO_ID
end

and I get an error like: Conversion failed when converting the nvarchar value 'xxxxxxxxx' to data type tinyint.

I don't know what your data types are, but it seems that all of them have to be the same and it looks like it is taking the data type of the field in the else part as the type for all of them when it is not specified.

When I did something like:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU, 
CASE WHEN 'a' = 'b' then Requester
     when 'b' = 'c' then BU
     else convert(nvarchar(50), ISO_ID)
end AS SortByField
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
     when 'b' = 'c' then BU
     else convert(nvarchar(50), ISO_ID)
end

and then did the changing of the letters in both cases, it seemed to work fine.

A possibly better way to code it would be:

select Requester, ISO, someNum, BU
FROM  
(select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU, 
CASE WHEN 'a' = 'b' then Requester
     when 'b' = 'c' then BU
     else convert(nvarchar(50), ISO_ID)
end AS SortByField
from LoanerHeader) AS dt 
order by SortByField

回答4:

I blogged about this a while ago. There is a very simple example with code and a thorough explanation of the problem.

SQL Server Case/When Data Type Problems