有一个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会报错