仅在使用CASE WHEN时SQL查询返回错误

I have the sql code below and the case when statement is always returning the 'Error converting data type varchar to numeric'. But if I comment it out then the statement runs successfully even though the same code is used in the first column.

我有下面的sql代码和case语句总是返回'错误将数据类型varchar转换为数字'的情况。但是，如果我将其注释掉，那么即使在第一列中使用了相同的代码，语句也会成功运行。

SELECT CAST(DATA1 AS DECIMAL(10,5)), AMOUNT, CASE WHEN NAME ='A' THEN CAST(DATA1 AS DECIMAL(10,5)) ELSE '0' END FROM TEST

If I run as is above, it fails. If I comment out the Case when piece then it runs successfully. Thanks!

如果我按上述方式运行，则会失败。如果我在片段时注释掉Case，那么它会成功运行。谢谢！

2 个解决方案

#1

You are telling it to display two different data types in the same column. Try;

您告诉它在同一列中显示两种不同的数据类型。尝试;

SELECT CAST(DATA1 AS DECIMAL(10,5)), AMOUNT, 
CASE 
  WHEN NAME LIKE 'A' THEN CAST(DATA1 AS DECIMAL(10,5)) 
  ELSE CAST(0.0 AS DECIMAL(10,5)) END FROM TEST

#2

Your CASE statement is trying to return two different types, DECIMAL(10,5) and varchar.

您的CASE语句正在尝试返回两种不同的类型：DECIMAL（10,5）和varchar。

It needs to return one type, so instead of having '0' just use 0.0.

它需要返回一种类型，所以不要使用'0'而是使用0.0。

#1