时间戳

首页 > 解决方案 > "CASE" in SQL Query not work with string values

sql - "CASE" in SQL Query not work with string values

问题描述

I have Simple “Case” query that work with number but not work with string?

It Work Correctly:

SELECT
OrderItem_ID,Cost,
Case
    When Cost = 0 then 555
    Else Cost
End As CostStatus
FROM
    OrderItem

It Doesn’t work:

    SELECT
    OrderItem_ID,Cost,
    Case
        When Cost = 0 then "Free"
        Else Cost
    End As CostStatus
    FROM
        OrderItem

Error is: Invalid column name 'Free'.

标签： sqlsql-serversql-server-2014

解决方案

All the results from a CASE expression should return the same data type or need to be implicitly converted following data precedence. In this case, numeric types have a higher precedence than string data types. Therefore, you need to use an explicit conversion.

SELECT
OrderItem_ID,Cost,
Case
    When Cost = 0 then 'Free'
    Else CAST( Cost AS varchar(13))
End As CostStatus
FROM
    OrderItem

EDIT: I changed the double quotes to single quotes which are the standard in T-SQL. Double quotes will only work if QUOTED_IDENTIFIERS is OFF. Both problems were present in the query presented.

推荐阅读