ORA-00932: 不一致的數據類型: 預期的 CHAR 得到了 NUMBER

我正在嘗試 SUBSTR 使用此查詢的前 3 個字元-> CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = ‘TestProgram’) THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1 但是它返回給我這個 ORA-00932：不一致的數據類型：預期的 CHAR 得到了 NUMBER 00932。00000 -“不一致的數據類型：預期的 %s 得到了 %s”。我可以知道我的查詢有什麼問題嗎？我嘗試使用 To_NUMBER 或 To CHAR，它也返回相同的錯誤。感謝有人可以幫助解決這個問題。以下是我的查詢：

SELECT RMSTMP_PNG.ota_activity.EQP_ID,
SUM(CASE WHEN (RMSTMP_PNG.ota_activity.MESSAGE='Load lot success.' AND RMSTMP_PNG.ota_activity_lotinfo.KEY = 'Quantity') THEN 1 ELSE 0 END) AS LOT_ID,
SUM(CASE WHEN (RMSTMP_PNG.ota_activity.MESSAGE='Load lot success.' AND RMSTMP_PNG.ota_activity_lotinfo.KEY = 'Quantity') THEN TO_NUMBER(RMSTMP_PNG.ota_activity_lotinfo.VALUE) ELSE 0 END) AS QUANTITY,
CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1
FROM RMSTMP_PNG.ota_activity 
FULL OUTER JOIN RMSTMP_PNG.ota_activity_lotinfo ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID 
WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE >= to_date('27-Sep-2020')
AND RMSTMP_PNG.ota_activity.MODIFIED_DATE < to_date('27-Sep-2020') + 1 
group by RMSTMP_PNG.ota_activity.EQP_ID,RMSTMP_PNG.ota_activity_lotinfo.VALUE, RMSTMP_PNG.ota_activity_lotinfo.KEY
order by RMSTMP_PNG.ota_activity.EQP_ID

CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = ‘TestProgram’) THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1

您的 case 語句返回一個字元（然後是 to_char(…)）和一個數字（否則為 0）。嘗試修改 else 條件以返回一個字元串：

CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE '0' END AS Test1

或返回數字的第一個條件：

CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_NUMBER(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1

引用自：https://dba.stackexchange.com/questions/276165