Sql – ORA-00932 inconsistent datatypes expected char got number

oraclesql

When I try to execute the query below I get the error:

ORA-00932 inconsistent datatypes expected char got number

select (case when upper (TEXT) <> lower (TEXT) then 'INVALID'
             else sum(TEXT)
        end) 
  from CLASS 
 where SECTION = 'SEVENTH'

The query works fine when I remove SUM in ELSE condition> But I need to SUM the Text to achieve the expected result.

Best Answer

You can't sum a character value and all the returned values in a CASE statement must be the same datatype.

If you transform your SUM to a character using TO_CHAR() this still won't work as you're not grouping correctly, see this SQL Fiddle.

The easiest way to do this would be to return a 0 rather than 'INVALID', sum over the entire case statement and change this back to 'INVALID' if it's 0. It's not quite the same... don't store numbers in character columns?

select case when a = 0 then 'INVALID'
            else to_char(a)
       end
  from ( select sum( case when upper(txt) <> lower(txt) then 0
                          else to_number(txt)
                     end ) as a
           from class )

Here's a SQL Fiddle to demonstrate.

Related Topic