关于union 中 ORA-12704:字符集不匹配问题的解决

在使用case when 生成的字符串union时，遇到了此问题，其实是因为生成的字符串与合并表的列格式不统一造成的，因此只需强制转换为目标列格式即可

原语句：

select
	to_char(a.org_id) entity_id,
	'机构' entity_sort,
	b.description level_name,
	to_char(a.org_level) group_level,
	to_char(a.org_name) entity_name
from pc_organization@linka2 a
left join pc_pk_org_level@linka2 b
on a.org_level=b.code
union
select
	to_char(site_id) entity_id,
	'工区' entity_sort,
	case 
		when p_site_type = 0 then '物探工区'
		when p_site_type = 1 then '井网'
		when p_site_type = 2 then '平台'
		when p_site_type = 3 then '通用井场'
	else
		null
	end level_name,
	to_char(p_site_type) group_level,
	to_char(site_name) entity_name
from cd_site_source@linka2

修改后：

select
	to_char(a.org_id) entity_id,
	'机构' entity_sort,
	b.description level_name,
	to_char(a.org_level) group_level,
	to_char(a.org_name) entity_name
from pc_organization@linka2 a
left join pc_pk_org_level@linka2 b
on a.org_level=b.code
union
select
	to_char(site_id) entity_id,
	'工区' entity_sort,
	case when p_site_type=0 then cast('物探工区' as nvarchar2(30))
		when p_site_type=1 then cast('井网' as nvarchar2(30))
		when p_site_type=2 then cast('平台' as nvarchar2(30))
		when p_site_type=3 then cast('通用井场' as nvarchar2(30))
	else
		null
	end level_name,
	to_char(p_site_type) group_level,
	to_char(site_name) entity_name
from cd_site_source@linka2