oracle数据库sql语句字符集不匹配错误，nvarchar2

1.问题

字符集不匹配

select pt.projectid, projectname, son.organname as constructioncompany,
case construction
when ‘1’ then ‘新建’
when ‘2’ then ‘续建’
when ‘3’ then ‘前期研究’
when ‘4’ then ‘预备’
end as construction, ndjsnr, to_char(scheduleddate, ‘yyyy-MM-dd’) as scheduleddate, to_char(estimatedendtime, ‘yyyy-MM-dd’) as estimatedendtime, totalinvestment
from project pt
left join sys_organization son

2.原因

关键字 when then else 后面的值的字符类型与字段 construction类型不一致，construction类型为nvarchar2，因此在比较时发生字符集不匹配错误。

3.解决方法

复杂方式：用to_char()函数将所有值改成同类型字符，string类型。
select pt.projectid, projectname, son.organname as constructioncompany,
case to_char(construction)
when to_char(‘1’) then to_char(‘新建’)
when to_char(‘2’) then to_char(‘续建’)
when to_char(‘3’) then to_char(‘前期研究’)
when to_char(‘4’) then to_char(‘预备’)
end as construction, ndjsnr, to_char(scheduleddate, ‘yyyy-MM-dd’) as scheduleddate, to_char(estimatedendtime, ‘yyyy-MM-dd’) as estimatedendtime, totalinvestment
from project pt
left join sys_organization son
on trim(pt.constructioncompany)=son.organid;
简单方式：用to_char()函数将字段construction变为string类型即可
select pt.projectid, projectname, son.organname as constructioncompany,
case to_char(construction)
when ‘1’ then ‘新建’
when ‘2’ then ‘续建’
when ‘3’ then ‘前期研究’
when ‘4’ then ‘预备’
end as construction, ndjsnr, to_char(scheduleddate, ‘yyyy-MM-dd’) as scheduleddate, to_char(estimatedendtime, ‘yyyy-MM-dd’) as estimatedendtime, totalinvestment
from project pt
left join sys_organization son
on trim(pt.constructioncompany)=son.organid;