CODEWITHSUNDEEP
sqloracle-database
kestrel
kestrel

Reputation: 126

Why does this CASE expression give an 'inconsistent datatypes' error?

I am trying to return an integer amount when the condition is false and a string when the condition is true.

SELECT 
    ENAME, 
    CASE WHEN COMM IS NULL THEN 'no commission' 
        ELSE COMM
    END AS COMMISSION 
FROM EMP

I am getting this error:

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

Upvotes: 1

Views: 2070

Answers (3)

miracle173
miracle173

Reputation: 1973

From the Oracle SQL Language Reference 18c:

For both simple and searched CASE expressions, all of the return_exprs must either have the same data type (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric data type. If all return expressions have a numeric data type, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

The datatype of of the string literal 'no commission' is VARCHAR2 and the column COMM seems to be a number data type, so an error is thrown.

You can convert the return value COMM in the else-clause to VARCHAR2, to avoid this error. I think the best way to do so is to use the TO_CHAR function because this lets you control the format of the numbers displayed.

Upvotes: 3

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

All datatypes of all cases must match whatever comparison structure you use such as case..when, decode, nvl or nvl2. I suggest you to use nvl or nvl2 for your case, since they're more direct for null value cases :

SELECT ENAME, 
       nvl(to_char(COMM),'no commission') AS COMMISSION,
       nvl2(COMM,to_char(COMM),'no commission') AS COMMISSION2     
  FROM EMP;

SQL Fiddle Demo

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175676

You need to use CAST:

SELECT ENAME, CASE WHEN COMM IS NULL THEN 'no commission' 
                   ELSE CAST(COMM AS VARCHAR2(20)) END AS COMMISSION 
FROM EMP;

Or use COALESCE:

SELECT ENAME, COALESCE(CAST(COMM AS VARCHAR2(20)), 'no commision')
FROM EMP;

Upvotes: 4

Related Questions