oracle ORA-00932 预期的数据类型不一致，字符得到数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17015044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00932 inconsistent datatypes expected char got number
提问by user2469253
When I try to execute the query below I get the error:
当我尝试执行下面的查询时，出现错误：
ORA-00932 inconsistent datatypes expected char got number
ORA-00932 预期的数据类型不一致，字符得到数字
select (case when upper (TEXT) <> lower (TEXT) then 'INVALID'
else sum(TEXT)
end)
from CLASS
where SECTION = 'SEVENTH'
The query works fine when I remove SUM in ELSE condition> But I need to SUM the Text to achieve the expected result.
当我在 ELSE 条件中删除 SUM 时，查询工作正常> 但我需要对文本求和才能达到预期的结果。
回答by Ben
You can't sum a character value and all the returned values in a CASE statement mustbe the same datatype.
您不能对字符值求和，并且 CASE 语句中的所有返回值必须是相同的数据类型。
If you transform your SUM to a character using TO_CHAR() this still won't work as you're not grouping correctly, see this SQL Fiddle.
如果您使用 TO_CHAR() 将 SUM 转换为字符，这仍然无法正常工作，因为您没有正确分组，请参阅此SQL Fiddle。
The easiest way to do this would be to return a 0 rather than 'INVALID', sum over the entire case statement and change this back to 'INVALID'if it's 0. It's not quite the same... don't store numbers in character columns?
最简单的方法是返回 0 而不是'INVALID'，对整个 case 语句求和并将其更改回'INVALID'0。这不太一样......不要在字符列中存储数字？
select case when a = 0 then 'INVALID'
else to_char(a)
end
from ( select sum( case when upper(txt) <> lower(txt) then 0
else to_number(txt)
end ) as a
from class )
Here's a SQL Fiddleto demonstrate.
这是一个用于演示的SQL Fiddle。
回答by kprobst
Well you can't SUMtext, right? That's why it works well when you remove the operation from the CASE. Have you tried using to_numberon the value you're trying to sum?
好吧，你不能发SUM短信，对吧？这就是为什么当您从CASE. 您是否尝试过使用要求to_number和的值？
