Case statement in Oracle with one condition returning the actual column

Question

I'm writing a CASE statement in ORACLE over TOAD which returns the actual value if it meets certain conditions otherwise returns a new string.

The following works,

SELECT (CASE WHEN COLUMN_NAME = 'SOMETEXT' THEN 'SOMEOTHERTEXT' ELSE 'DIFFERENTTEXT' END) NEWCOLUMNNAME
FROM TABLENAME

The following does not work,

SELECT (CASE WHEN COLUMN_NAME = 'SOMETEXT' THEN 'SOMEOTHERTEXT' ELSE COLUMN_NAME END) NEWCOLUMNNAME
FROM TABLENAME

I get the following error -

ORA-12704: character set mismatch

Any help?

Answer 1

Mix of varchar and nvarchar at the result type.
The default type for your string literals is varchar and your column is of nvarchar type.

Put N before the string literals in order to define them as nvarchar.

https://docs.oracle.com/cd/E18283_01/server.112/e17118/sql_elements003.htm#i42617

---

create table TABLENAME (COLUMN_NAME nvarchar2(100));
insert into TABLENAME (COLUMN_NAME) values ('ABC');


select  case 
            when column_name = 'SOMETEXT' 
            then 'SOMEOTHERTEXT' 
            else column_name 
        end                     as  newcolumnname

from    tablename
;

ORA-12704: character set mismatch

(The 1st N is to prevent implicit cast for the comparison, the 2nd N prevents the error - all result expressions of the case statement should be of the same type)

select  case 
            when column_name = N'SOMETEXT' 
            then N'SOMEOTHERTEXT' 
            else column_name 
        end                     as  newcolumnname

from    tablename
;

---

NEWCOLUMNNAME
-------------
ABC