sqloracle-databaseoracle-sqldevelopercase-when

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

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

Solution

  • 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