在mssql中选择case语句错误

SELECT top 1 
    case 
        when VR = -99999.99 
        then 0 
        else cast((VR*1.732) as decimal(38,3)) 
    end 
FROM pseb.dbo.datasource 
where FeederID=5003
order by datetime desc

The above query is working fine, but I need to return varchar value '--' instead of returning 0

上面的查询工作正常，但我需要返回varchar值' - '而不是返回0

if I do like that

如果我喜欢那样的话

SELECT top 1 
    case 
        when VR = -99999.99 
        then '--' 
        else cast((VR*1.732) as decimal(38,3)) 
    end 
FROM pseb.dbo.datasource 
where FeederID=5003
order by datetime desc

means it returns the following error:

表示它返回以下错误：

Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.

消息8114，级别16，状态5，行1错误将数据类型varchar转换为数字。

please help me to solve it

请帮我解决一下

1 个解决方案

#1

The problem is that you are returning two different data types from the same column. The rule with SQL Server that numeric types take precedence over string types, i.e. in a situation like yours a string gets converted to a number, not the other way around.

问题是您从同一列返回两种不同的数据类型。使用SQL Server的规则，数字类型优先于字符串类型，即在像你这样的情况下，字符串会转换为数字，而不是相反。

So to solve this you can cast your number to a string.

因此，要解决此问题，您可以将数字转换为字符串。

One option is to do something like this:

一种选择是做这样的事情：

SELECT top 1 
   case when VR = -99999.99 then '--' 
    else 
     cast
      ( 
        cast((VR*1.732) as decimal(38,3)
      ) 
     as varchar(50)) 
    end 
  FROM pseb.dbo.datasource where FeederID=5003 order by datetime desc

#1