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.

Answer 3

I got the solution for this problem from here [https://stackguides.com/questions/29803977]

As the answer stated :

Blockquote It seems Oracle requires for CASE structures the type of all THEN expressions to be consistent with the TYPE of the expression after the first THEN. No type conversions are performed.

This is short and reason of the error. Basically, The data type of the value you mention after first THEN part has to be same in all following THENs of the same level.

Answer 4

try using to_number(t.amount)

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

Answer 5

I couldn't convert my string value to number, so I converted the number to string using TO_CHAR function.