CODEWITHSUNDEEP
sqloracle-database
tempidope
tempidope

Reputation: 873

Case statement in Oracle with one condition returning the actual column

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?

Upvotes: 6

Views: 4350

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

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

Upvotes: 7

Related Questions