Oracle创建视图提示‘已编译但存在编译错误’求助

问题描述 #

我在Toad工具中执行视图创建脚本时，收到如下警告信息：

Warning: compiled but with compilation error in oracle

视图创建脚本 #

CREATE OR REPLACE FORCE VIEW APPS.KMI_OKP_TRACK_V ( NO_OKP, LINE, FBD_PSD, FBD_ASD, FBD_PCD, FBD_ACD, FBD_DD, FBD_BCD, MIX_PSD, MIX_ASD, MIX_PCD, MIX_ACD, MIX_DD, MIX_BCD, BPR_PSD, BPR_ASD, BPR_PCD, BPR_ACD, BPR_DD, BPR_BCD, BLD_PSD, BLD_ASD, BLD_PCD, BLD_ACD, BLD_DD, BLD_BCD, CAN_PSD, CAN_ASD, CAN_PCD, CAN_ACD, CAN_DD, CAN_BCD, BAG_PSD, BAG_ASD, BAG_PCD, BAG_ACD, BAG_DD, BAG_BCD, DPG_PSD, DPG_ASD, DPG_PCD, DPG_ACD, DPG_DD, DPG_BCD, SAC_PSD, SAC_ASD, SAC_PCD, SAC_ACD, SAC_DD, SAC_BCD, BPE_PSD, BPE_ASD, BPE_PCD, BPE_ACD, BPE_DD, BPE_BCD ) AS SELECT DISTINCT XX.NO_OKP, XX.LINE --,XX.CREATION_DATE , FBD.FBD_PSD, FBD.FBD_ASD, FBD.FBD_PCD, FBD.FBD_ACD, FBD.FBD_DD, FBD.FBD_BCD, MIX.MIX_PSD, MIX.MIX_ASD, MIX.MIX_PCD, MIX.MIX_ACD, MIX.MIX_DD, MIX.MIX_BCD, BPR.BPR_PSD, BPR.BPR_ASD, BPR.BPR_PCD, BPR.BPR_ACD, BPR.BPR_DD, BPR.BPR_BCD, BLD.BLD_PSD, BLD.BLD_ASD, BLD.BLD_PCD, BLD.BLD_ACD, BLD.BLD_DD, BLD.BLD_BCD, CAN.CAN_PSD, CAN.CAN_ASD, CAN.CAN_PCD, CAN.CAN_ACD, CAN.CAN_DD, CAN.CAN_BCD, BAG.BAG_PSD, BAG.BAG_ASD, BAG.BAG_PCD, BAG.BAG_ACD, BAG.BAG_DD, BAG.BAG_BCD, DPG.DPG_PSD, DPG.DPG_ASD, DPG.DPG_PCD, DPG.DPG_ACD, DPG.DPG_DD, DPG.DPG_BCD, SAC.SAC_PSD, SAC.SAC_ASD, SAC.SAC_PCD, SAC.SAC_ACD, SAC.SAC_DD, SAC.SAC_BCD, BPE.BPE_PSD, BPE.BPE_ASD, BPE.BPE_PCD, BPE.BPE_ACD, BPE.BPE_DD, BPE.BPE_BCD FROM ( SELECT DISTINCT RTRIM ( SUBSTR (BATCH_NO, 4, 4), 'A' ) no_okp, ATTRIBUTE1 LINE --,CREATION_DATE FROM GME_BATCH_HEADER WHERE ORGANIZATION_ID = 84 AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN (TO_CHAR ( SYSDATE - 7, 'MM/DD/YYYY' ), TO_CHAR ( SYSDATE - 8, 'MM/DD/YYYY' )) ORDER BY CREATION_DATE DESC) XX LEFT OUTER JOIN ( SELECT DISTINCT RTRIM ( SUBSTR (BATCH_NO, 4, 4), 'A' ) no_okp, attribute1 Line, DECODE (batch_status, '1', 'Inspect', '2', 'Release', '3', 'Complete', 'Close') Batch_status, CREATION_DATE, PLAN_START_DATE FBD_PSD, ACTUAL_START_DATE FBD_ASD, PLAN_CMPLT_DATE FBD_PCD, ACTUAL_CMPLT_DATE FBD_ACD, DUE_DATE FBD_DD, BATCH_CLOSE_DATE FBD_BCD FROM GME_BATCH_HEADER WHERE ORGANIZATION_ID = 84 AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN (TO_CHAR ( SYSDATE - 7, 'MM/DD/YYYY' ), TO_CHAR ( SYSDATE - 8, 'MM/DD/YYYY' )) AND attribute1 = 'FAT BLEND' ORDER BY CREATION_DATE DESC) FBD ON XX.NO_OKP = FBD.NO_OKP LEFT OUTER JOIN ( SELECT DISTINCT RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A') no_okp, attribute1 Line, DECODE (batch_status, '1', 'Inspect', '2', 'Release', '3', 'Complete', 'Close') Batch_status, CREATION_DATE, PLAN_START_DATE MIX_PSD, ACTUAL_START_DATE MIX_ASD, PLAN_CMPLT_DATE MIX_PCD, ACTUAL_CMPLT_DATE MIX_ACD, DUE_DATE MIX_DD, BATCH_CLOSE_DATE MIX_BCD FROM GME_BATCH_HEADER WHERE ORGANIZATION_ID = 84 AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN (TO_CHAR ( SYSDATE - 7, 'MM/DD/YYYY' ), TO_CHAR ( SYSDATE - 8, 'MM/DD/YYYY' )) AND attribute1 = 'MIX' ORDER BY CREATION_DATE DESC) MIX ON XX.NO_OKP = MIX.NO_OKP LEFT OUTER JOIN ( SELECT DISTINCT RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A') no_okp, attribute1 Line, DECODE (batch_status, '1', 'Inspect', '2', 'Release', '3', 'Complete', 'Close') Batch_status, CREATION_DATE, PLAN_START_DATE BPR_PSD, ACTUAL_START_DATE BPR_ASD, PLAN_CMPLT_DATE BPR_PCD, ACTUAL_CMPLT_DATE BPR_ACD, DUE_DATE BPR_DD, BATCH_CLOSE_DATE BPR_BCD FROM GME_BATCH_HEADER WHERE ORGANIZATION_ID = 84 AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN (TO_CHAR (SYSDATE - 7, 'MM/DD/YYYY'), TO_CHAR (SYSDATE - 8, 'MM/DD/YYYY')) AND attribute1 = 'BASE POWDER' ORDER BY CREATION_DATE DESC) BPR ON XX.NO_OKP = BPR.NO_OKP LEFT OUTER JOIN ( SELECT DISTINCT RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A') no_okp, attribute1 Line, DECODE (batch_status, '1', 'Inspect', '2', 'Release', '3', 'Complete', 'Close') Batch_status, CREATION_DATE, PLAN_START_DATE BLD_PSD, ACTUAL_START_DATE BLD_ASD, PLAN_CMPLT_DATE BLD_PCD, ACTUAL_CMPLT_DATE BLD_ACD, DUE_DATE BLD_DD, BATCH_CLOSE_DATE BLD_BCD FROM GME_BATCH_HEADER WHERE ORGANIZATION_ID = 84 AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN (TO_CHAR (SYSDATE - 7, 'MM/DD/YYYY'), TO_CHAR (SYSDATE - 8, 'MM/DD/YYYY')) AND attribute1 = 'BLEND' ORDER BY CREATION_DATE DESC) BLD ON XX.NO_OKP = BLD.NO_OKP LEFT OUTER JOIN ( SELECT DISTINCT RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A') no_okp, attribute1 Line, DECODE (batch_status, '1', 'Inspect', '2', 'Release', '3', 'Complete', 'Close') Batch_status, CREATION_DATE, PLAN_START_DATE CAN_PSD, ACTUAL_START_DATE CAN_ASD, PLAN_CMPLT_DATE CAN_PCD, ACTUAL_CMPLT_DATE CAN_ACD, DUE_DATE CAN_DD, BATCH_CLOSE_DATE CAN_BCD FROM GME_BATCH_HEADER WHERE ORGANIZATION_ID = 84 AND TO_CHAR (creation_date, 'MM/DD/YYYY') IN (TO_CHAR (SYSDATE - 7, 'MM/DD/YYYY'), TO_CHAR (SYSDATE - 8, 'MM/DD/YYYY')) AND attribute1 = 'CANNING' ORDER BY CREATION_DATE DESC) CAN ON XX.NO_OKP = CAN.NO_OKP LEFT OUTER JOIN ( SELECT DISTINCT RTRIM (SUBSTR (BATCH_NO, 4, 4), 'A') no_okp, attribute1 Line, DECODE (batch_status, '1', 'Inspect', '2', 'Release', '3', 'Complete',

请求协助 #

请帮忙排查该视图编译错误的原因并提供对应的解决办法。

内容的提问来源于stack exchange，提问作者Makhali