错误信息 1292 - Truncated incorrect INTEGER value: 'F161200101008'
源代码是这样的
INSERT INTO ca_pay_batch (
BATCH_NO,
BATCH_DATE
, LOAN_CNT
, SUC_CNT
, FAIL_CNT
, AMOUNT_SUM
, STATUS
, CREATER
, UPDATE_TIME
, CREATE_TIME
) SELECT
'' BATCH_NO,
SYSDATE() BATCH_DATE,
count( case when sd.loan_type='CF' and IFNULL(c.SER_FIN_REBATE, 0)>0 then c.APP_CODE
when sd.loan_type='MF' then c.APP_CODE end
) LOAN_CNT,
0 SUC_CNT,
0 FAIL_CNT,
sum(distinct c.R_LOAN_AMOUNT) AMOUNT_SUM,
'0' STATUS,
d.usernames CREATER,
SYSDATE() UPDATE_TIME,
SYSDATE() CREATE_TIME
FROM
c
INNER JOIN tmp_print d ON c.APP_CODE = d.appno
LEFT JOIN SD ON C.APP_CODE = SD.APP_CODE
where c.STATUS='19'
GROUP BY d.usernames;
折腾了半天 发现 是 count( case when sd.loan_type='CF' and IFNULL(c.SER_FIN_REBATE, 0)>0 then c.APP_CODE
when sd.loan_type='MF' then c.APP_CODE end
) LOAN_CNT, 这个字段引起的报错,之前也是正常的,突然的报这个错误,后来改成
count( case when sd.loan_type='CF' and IFNULL(c.SER_FIN_REBATE, 0)>0 then 1
when sd.loan_type='MF' then 1 end
) LOAN_CNT, 算是暂时稳住了,
插入的目标表设计:
感觉是默认null引起的连锁反应,生产库没敢测试