Oracle SQL CASE WHEN ORA-00932：不一致的数据类型：预期的 CHAR 得到 NUMBER 00932. 00000 -“不一致的数据类型：预期的 %s 得到 %s”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26678672/
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
Oracle SQL CASE WHEN ORA-00932: inconsistent datatypes: expected CHAR got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s"
提问by Matt
Getting error
获取错误
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s"
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s"
When i run the following query
当我运行以下查询时
SELECT distinct
CASE when t.cancelled = 'TRUE' then '0'
else t.amount END AMOUNT,
FROM table t
If i run it with either a number or text for the else output, like this, it works.
如果我为 else 输出使用数字或文本运行它，就像这样，它可以工作。
SELECT distinct
CASE when t.cancelled = 'TRUE' then '0'
else 'xxx' END AMOUNT,
FROM table t
回答by radar
Use 0instead of '0'. Amount is a number, and numbers aren't quoted.
使用0代替'0'。金额是一个数字，数字没有被引用。
SELECT distinct
CASE when t.cancelled = 'TRUE' then 0
else t.amount END AMOUNT,
FROM table t
回答by Lalit Kumar B
In addition to the answer by @RADAR,
除了@RADAR 的回答，
The reason for the error is that t.amountfield is a NUMBERdata type and not a string.
错误的原因是该t.amount字段是一种NUMBER数据类型而不是string.
Your CASEexpression is internally trying to fit a STRINGin a NUMBERdata type.
您的CASE表达式正在内部尝试将 aSTRING放入NUMBER数据类型中。
As already suggested in RADAR's answer, use zero as a number and NOT as a string.
正如 RADAR 的回答中已经建议的那样，使用零作为数字而不是字符串。
回答by khalidmehmoodawan
I got the solution for this problem from here [https://stackoverflow.com/questions/29803977]
我从这里得到了这个问题的解决方案 [ https://stackoverflow.com/questions/29803977]
As the answer stated :
正如答案所说：
Blockquote It seems Oracle requires for CASE structures the type of all THEN expressions to be consistent with the TYPE of the expression after the first THEN. No type conversions are performed.
Blockquote 似乎 Oracle 要求 CASE 结构的所有 THEN 表达式的类型与第一个 THEN 之后的表达式的 TYPE 一致。不执行类型转换。
This is short and reason of the error. Basically, The data type of the value you mention after first THEN part has to be same in all following THENs of the same level.
这是错误的原因。基本上，您在第一个 THEN 部分之后提到的值的数据类型在相同级别的所有后续 THEN 中必须相同。
回答by Line
回答by Shravan Kumar
try using to_number(t.amount)
尝试使用 to_number(t.amount)
SELECT distinct
CASE when t.cancelled = 'TRUE' then 0
else to_number(t.amount) END AMOUNT,
FROM table t
