Case statement in Oracle with one condition returning the actual column

Question

I am writing a CASE statement in ORACLE over TOAD, which returns the actual value if it meets certain conditions, otherwise it returns a new line.

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

The combination of varchar and nvarchar in the result type.
The default type for your string literals is varchar, and your column is of type nvarchar.

Place Nbefore string literals 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:

(1- N , N - case )

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

from    tablename
;

---

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