ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 - "inconsistent datatypes: expected %s got %s"
1 CASE XXXXX.FILE_TYPE 2 WHEN '0' THEN NVL(TX_CNT,0) 3 ELSE XXXXX.TOT_TX_CNT 4 END ACT_TX_CNT,
TX_CNT为NUMBER型，是GROUP BY 后的计数量
TOT_TX_CNT是VARCHAR2(XX)类型
故出现上述错误提示。
解决方案：进行数据转换，将NUMBER型数据转换成VACHAR2(XX)类型
1 CASE XXXXX.FILE_TYPE 2 WHEN '0' THEN CAST( NVL(ACT_TX_CNT,0) AS VARCHAR2(8)) 3 ELSE EPSTFBOK.TOT_TX_CNT 4 END ACT_TX_CNT,
完
完整代码小记：
统计文件对应的交易笔数，交易金额，有效的交易笔数，有效的交易金额
1 SELECT EPSTFBOK.FILE_DT, 2 EPSTFBOK.FILE_TYPE, 3 EPSTFBOK.FILE_SRC, 4 EPSTFBOK.FIL_STS, 5 EPSTFBOK.RCV_FIL, 6 EPSTFBOK.TOT_TX_CNT, 7 EPSTFBOK.TOT_TX_AMT, 8 EPSTFBOK.RMK, 9 EPSTFBOK.BAT_NO, 10 nvl( CHUI_CNT,0) CHUI_CNT, 11 CASE EPSTFBOK.FILE_TYPE WHEN '0' THEN CAST( NVL(ACT_TX_CNT,0) AS VARCHAR2(8)) ELSE EPSTFBOK.TOT_TX_CNT END ACT_TX_CNT, 12 CASE EPSTFBOK.FILE_TYPE WHEN '0' THEN CAST(NVL(ACT_TX_AMT,0) AS VARCHAR(15)) ELSE EPSTFBOK.TOT_TX_AMT END ACT_TX_AMT 13 FROM EPSTFBOK 14 LEFT JOIN 15 (SELECT BAT_NO,COUNT(*) CHUI_CNT FROM EPSTORDR WHERE ALT_FLG='1' GROUP BY BAT_NO) 16 M ON epstfbok.bat_no = m.bat_no 17 LEFT JOIN 18 (SELECT BAT_NO,COUNT(*) ACT_TX_CNT, SUM(TX_AMT) ACT_TX_AMT FROM EPSTORDR GROUP BY BAT_NO ) 19 N ON epstfbok.bat_no = N.bat_no 20 ORDER BY EPSTFBOK.BAT_NO desc;