关于ORACLE UNION ALL 遇到的问题

最新推荐文章于 2022-05-30 17:22:52 发布

枫雨无痕

最新推荐文章于 2022-05-30 17:22:52 发布

阅读量2.4k

点赞数

分类专栏：数据库文章标签： oracle join user file delete path

本文链接：https://blog.csdn.net/leozhk/article/details/6265772

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

前两天在写视图的时候，用到了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'