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

Question

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

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

Answer 1

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

Answer 2

In addition to the answer by @RADAR,

The reason for the error is that t.amount field is a NUMBER data type and not a string.

Your CASE expression is internally trying to fit a STRING in a NUMBER data type.

As already suggested in RADAR's answer, use zero as a number and NOT as a string.