原因:(''默认的是VARCHAR2,与NVARCHAR2不匹配)
表1有字段:LEGAL_PERSON NVARCHAR2(32)
表2无此字段 union时用''代替
解决办法:
使用cast()将字符集转换为相同
--oc.legal_person, -- 法人
cast(oc.legal_person as NVARCHAR2(32)) legal_person, -- 法人
--'' legal_person, -- 法人
cast('' as NVARCHAR2(32)) legal_person, -- 法人
成功实例
--“组织管理”视图oms_company_department_v
create or replace view oms_company_department_v as
select
a.ORGANIZATION_ID,
a.ORGANIZATION_CODE,
a.ORGANIZATION_NAME,
a.ORGANIZATION_PARENT_ID,
a.ORGANIZATION_PARENT_NAME,
a.ORGANIZATION_TYPE,
a.ENABLED_FLAG,
a.legal_person,
a.customed,
a.parent_id_path,
a.parent_name_path,
a.depts_company,
(select count(1)from
(select oc.company_id organization_id,
oc.parent_id organization_parent_id -- 父ID
from oms_company oc
where oc.enabled_flag = 1
union all
select od.department_id organization_id, -- ID
od.parent_id organization_parent_id -- 父ID
from oms_department od
where od.enabled_flag = 1) b
where a.organization_id = b.organization_parent_id) has_child
from (select oc.company_id organization_id, -- ID
oc.company_code organization_code, -- 编码
oc.company_name organization_name, -- 名称
oc.parent_id organization_parent_id, -- 父ID
(select t.company_name from oms_company t where t.company_id=oc.parent_id) organization_parent_name, -- 父名称
1 organization_type, -- 类型 1:代表单位 0 :代表部门
--oc.legal_person, -- 法人
cast(oc.legal_person as NVARCHAR2(32)) legal_person, -- 法人
oc.enabled_flag,
oc.customed,
oc.parent_id_path,
oc.parent_name_path,
null depts_company
from oms_company oc
where oc.enabled_flag = 1
union all
select od.department_id organization_id, -- ID
od.department_code organization_code, -- 编码
od.department_name organization_name, -- 名称
od.parent_id organization_parent_id, -- 父ID
(select t.department_name from oms_department t where t.department_id=od.parent_id) organization_parent_name, -- 父名称或者其上级单位名称
0 organization_type, -- 类型 1 :代表单位 0 :代表部门
--'' legal_person, -- 法人
cast('' as NVARCHAR2(32)) legal_person, -- 法人
od.enabled_flag,
od.customed,
od.parent_id_path,
od.parent_name_path,
od.company_id depts_company
from oms_department od
where od.enabled_flag = 1) a;
参考文献:
https://blog.csdn.net/baidu_37107022/article/details/81259319