ORA-00932:数据类型不一致:预期的 CHAR 获得编号

Question

Logic

逻辑是，如果订单被取消，则返回 0，否则返回欠值 - 已支付价值

小查询

CASE WHEN d.cancelled = 'TRUE' 
     THEN '0' 
     ELSE (to_char(b.owed)) - (to_char(d.paid)) 
     END AS balance,

获取错误

ORA-00932：数据类型不一致：预期的 CHAR 获得编号 00932. 00000 - "不一致的数据类型：预期的 %s 得到 %s" *原因：
*行动： 行错误： 25 列： 58

Answer 1

试试这个，要么你的案例应该返回数字或 varchar，现在你的案例返回"0"作为 varchar，否则作为数字。要么两者都应该返回一个 varchar，要么都应该返回一个数字。

当两者都返回瓦尔查尔时

CASE WHEN d.cancelled = 'TRUE' 
     THEN '0' 
     ELSE to_char((to_char(b.owed)) - (to_char(d.paid)))
     END AS balance,

或

当两者都返回编号

CASE WHEN d.cancelled = 'TRUE' 
         THEN 0 
         ELSE (to_char(b.owed)) - (to_char(d.paid))
         END AS balance,

或

当两者都返回编号

CASE WHEN d.cancelled = 'TRUE' 
         THEN 0 
         ELSE (b.owed - d.paid)
         END AS balance,

Answer 2

这个(to_char(b.owed)) - (to_char(d.paid))将是一个数字，0是一个字符串。您应该删除then子句中的勾号或 else 中的to_char子句：to_char((to_char(b.owed)) - (to_char(d.paid)))或干脆(b.owed - d.paid)