天使羊波波闪耀光芒 软件及互联网爱好者

2112月/160

Oracle sum字段报错 ORA-01722: invalid number

有一个sql要统计多个字段的sum值:

select card_accp_id c1,date_settlmt_8, sum(amt_trans), sum(mcht_fee), sum(host_trans_fee1)
from bth_new_gc_txn_succ where date_settlmt_8 > '20160101' group by card_accp_id,date_settlmt_8

执行报错:

14:14:36 [SELECT - 0 row(s), 0.000 secs] [Error Code: 1722, SQL State: 42000] ORA-01722: invalid number
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

解决:
原因是sum字段有空格,在host_trans_fee1上加个trim()

结果:

select card_accp_id c1,date_settlmt_8, sum(amt_trans), sum(mcht_fee), sum(trim(host_trans_fee1))
from bth_new_gc_txn_succ where date_settlmt_8 > '20160101' group by card_accp_id,date_settlmt_8

sum字段如果遇到有记录为非number会报错

评论 (0) 引用 (0)

还没有评论.


Leave a comment

:wink: :-| :-x :twisted: :) 8-O :( :roll: :-P :oops: :-o :mrgreen: :lol: :idea: :-D :evil: :cry: 8) :arrow: :-? :?: :!:

还没有引用.