ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

oracleoracle-10goracle-12c

I am trying to SUBSTR the first 3 character in use this query -> 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
But it return me this ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 – "inconsistent datatypes: expected %s got %s". May i know what wrong in my query? I tried to use To_NUMBER or To CHAR, it also return me same error. Appreciate if someone could help on this issue.
Below is my query:

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

Best Answer

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

Your case statement returns both a character (then to_char(...)) and a number (else 0). Try modifying the else condition to return a string:

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

or the first condition to return a number:

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
Related Question