oracle使用case when报错ORA-12704字符集不匹配原因分析及解决方法

问题概述

使用oracle的case when函数时，报错提示ORA-12704字符集不匹配，如下图，接下来分析报错原因并提出解决方法。

样例演示

现在有一个TESTTABLE表，本表包含的字段如下图所示，COL01字段是NVARCHAR2类型，COL02字段是VARCHAR2类型。

场景一

使用case简单函数，case后面的内容和when后面的内容，不能同时包含NVARCHAR2类型和其他类型数据（比如VARCHAR2或者单引号包围的常量），否则会报错ORA-12704字符集不匹配。

--执行报错：case后面的col01是NVARCHAR2类型，when后面是单引号包围的常量。

select case col01 when '是' then 'Y' else 'N' end as ret from TESTTABLE;

--执行报错：case后面的col01是NVARCHAR2类型，when后面的col02是VARCHAR2类型。
select case col01 when col02 then 'Y' else 'N' end as ret from TESTTABLE;

--执行成功：case后面的col01和when后面的col01都是NVARCHAR2类型。
select case col01 when col01 then 'Y' else 'N' end as ret from TESTTABLE;

--执行成功：case后面的col02和when后面的col02都是VARCHAR2类型。
select case col02 when col02 then 'Y' else 'N' end as ret from TESTTABLE;

场景二

使用case搜索函数，when后面条件中的内容可以同时包含NVARCHAR2类型和其他类型数据（比如VARCHAR2或者单引号包围的常量），可以正常执行，不会报错ORA-12704字符集不匹配。

--成功：when后面，等号左边的col01是NVARCHAR2类型，等号右边是单引号包围的常量。

select case when col01='是' then 'Y' else 'N' end as ret from TESTTABLE;

--成功：when后面，等号左边的col01是NVARCHAR2类型，等号右边的col02是VARCHAR2类型。
select case when col01=col02 then 'Y' else 'N' end as ret from TESTTABLE;

--成功：when后面，等号左边的col02是VARCHAR2类型，等号右边是单引号包围的常量。
select case when col02='是' then col01 else col01 end as ret from TESTTABLE;

场景三

在case when函数中,then或else后面的内容，不能同时包含NVARCHAR2类型和其他类型数据（比如VARCHAR2或者单引号包围的常量），否则会报错ORA-12704字符集不匹配。

--报错：then后面的col01字段是NVARCHAR2类型，else后面是单引号包围的常量。

select case when col01='是' then col01 else 'N' end as ret from TESTTABLE;

--报错：then后面的col01是NVARCHAR2类型，else后面的col02是VARCHAR2类型。
select case when col01='是' then col01 else col02 end as ret from TESTTABLE;

--报错：第一个then后面以及else后面的col01是NVARCHAR2类型，第二个then后面的col02是VARCHAR2类型。
select case when col01='是' then col01 when col01='否' then col02 else col01 end as ret from TESTTABLE;

--成功：所有then或else后面的内容都不是NVARCHAR2类型数据。
select case when col01='是' then 'Y' when col01='否' then col02 else 'N' end as ret from TESTTABLE;

--成功：所有then或else后面的内容都是NVARCHAR2类型数据。
select case when col01='是' then col01 when col01='否' then col01 else col01 end as ret from TESTTABLE;

解决方法

--方法1：用to_char函数对NVARCHAR2类型数据进行转换。
select case to_char(col01) when '是' then 'Y' when col02 then 'N' else col02 end as ret from TESTTABLE;

--方法2：用cast函数将其他类型转换为NVARCHAR2类型。
select case col01 when cast('是' as nvarchar2(10)) then 'Y' when cast(col02 as nvarchar2(10)) then 'N' else col02 end as ret from TESTTABLE;