Oracle SQL CASE WHEN ORA-00932: inconsistent datatypes: expected CHAR got NUMBER 00932. 00000 – “inconsistent datatypes: expected %s got %s”

Getting error

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s"

When i run the following query

SELECT distinct 
CASE when t.cancelled = 'TRUE' then '0' 
else t.amount END AMOUNT,
FROM table t

​x
 
SELECT distinct CASE when t.cancelled = 'TRUE' then '0' else t.amount END AMOUNT,FROM table t​

If i run it with either a number or text for the else output, like this, it works.

SELECT distinct 
CASE when t.cancelled = 'TRUE' then '0' 
else 'xxx' END AMOUNT,
FROM table t

 
SELECT distinct CASE when t.cancelled = 'TRUE' then '0' else 'xxx' END AMOUNT,FROM table t​

Answer

Use 0 instead of '0'. Amount is a number, and numbers aren’t quoted.

SELECT distinct 
CASE when t.cancelled = 'TRUE' then 0 
else t.amount END AMOUNT,
FROM table t

 
SELECT distinct CASE when t.cancelled = 'TRUE' then 0 else t.amount END AMOUNT,FROM table t​