ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

Question

Logic

The logic is if an order is cancelled then return 0 otherwise return the owed value - the paid value

Small query

CASE WHEN d.cancelled = 'TRUE' 
     THEN '0' 
     ELSE (to_char(b.owed)) - (to_char(d.paid)) 
     END AS balance,

Getting the error

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 25 Column: 58

Assuming the to_char function converts the data to a character data from numeric, wouldn't you wnat to do teh subtraction first then the conversion rather than convert and then try to subtract? — HLGEM
Is your goal to return a NUMBER as the balance? Or a VARCHAR2? Your THEN is returning a VARCHAR2, your ELSE is returning a NUMBER. You'll need to ensure that both paths return a value with the same data type. It's just not clear which data type you intend to return. — Justin Cave

HaveNoDisplayName HaveNoDisplayName · Accepted Answer · 2015-01-06T15:52:57

Try this, either your case should return number or varchar, right now your case return '0' as varchar and else as number. Either both should return a varchar or both should return a number.

When Both return varchar

CASE WHEN d.cancelled = 'TRUE' 
     THEN '0' 
     ELSE to_char((to_char(b.owed)) - (to_char(d.paid)))
     END AS balance,

OR

When Both return number

CASE WHEN d.cancelled = 'TRUE' 
         THEN 0 
         ELSE (to_char(b.owed)) - (to_char(d.paid))
         END AS balance,

OR

When Both return number

CASE WHEN d.cancelled = 'TRUE' 
         THEN 0 
         ELSE (b.owed - d.paid)
         END AS balance,

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

2 Answers