前两天在写视图的时候，用到了UNION ALL 发现了一个不注意的事情，除了我知道的需要列一样，如果union all的两条查询语句的列对应的数据类型不一样也不能通过会报：ORA-12704: 字符集不匹配这个错误；我们必须要使用cast 转换成相同的数据类型才行，具体方式请开如下代码：SELECT PROJ2.PROJECT_CODE XMBM,
PROJ1.PROJECT_CODE DTGCBH,
'' WJLX,
CAST (BS.BIAOD_ID BDBM as varchar2(20)),
FILE_.FILE_NAME WJMC,
FILE_.FILE_PATH WJDZ,
USER_.USER_NAME BZR,
ORG.ORGANIZATION_NAME BZRDW,
FILE_.CREATE_TIME SZSJ
FROM T_JZL_GONGCJGYSSQ BS
INNER JOIN
T_BNS_PRJM_PROJECT PROJ1
ON BS.GONGC_ID = PROJ1.ID
INNER JOIN
T_BNS_PRJM_PROJECT PROJ2
ON PROJ1.SUPER_PROJECTID = PROJ2.ID
LEFT JOIN
T_CTL_FILES FILE_
ON FILE_.GROUPID = BS.GROUPID
LEFT JOIN
T_PDC_USER USER_
ON USER_.ID = BS.BAOS_SENDER
LEFT JOIN
T_PDC_ORGANIZATION ORG
ON ORG.ID IN
(SELECT ORGANIZATION_ID
FROM T_PDC_ORGANIZATION_USER
WHERE USER_ID = BS.BAOS_SENDER AND DELETE_FLAG = '0')
WHERE BS.DELETE_FLAG = '0'
AND PROJ1.DELETE_FLAG = '0'
AND PROJ2.DELETE_FLAG = '0'
UNION ALL
SELECT PROJ2.PROJECT_CODE XMBM,
PROJ1.PROJECT_CODE DTGCBH,
'02' WJLX,
CAST ('' as varchar2(20)) BDBM,
FILE_.FILE_NAME WJMC,
FILE_.FILE_PATH WJDZ,
USER_.USER_NAME BZR,
ORG.ORGANIZATION_NAME BZRDW,
FILE_.CREATE_TIME SZSJ
FROM T_YXM_JINGYSSQ SQ
INNER JOIN
T_BNS_PRJM_PROJECT PROJ1
ON SQ.GONGC_ID = PROJ1.ID
INNER JOIN
T_BNS_PRJM_PROJECT PROJ2
ON PROJ1.SUPER_PROJECTID = PROJ2.ID
LEFT JOIN
T_CTL_FILES FILE_
ON FILE_.GROUPID = SQ.GROUPID
LEFT JOIN
T_PDC_USER USER_
ON USER_.ID = SQ.SIGLE_NAME
LEFT JOIN
T_PDC_ORGANIZATION ORG
ON ORG.ID IN
(SELECT ORGANIZATION_ID
FROM T_PDC_ORGANIZATION_USER
WHERE USER_ID = SQ.SIGLE_NAME AND DELETE_FLAG = '0')
WHERE SQ.DELETE_FLAG = '0'
AND PROJ1.DELETE_FLAG = '0'
AND PROJ2.DELETE_FLAG = '0'
转播
