oracle SQL ORA-02063 - 如何解决这个问题？

SQL ORA-02063 - How to fix this?

提问by Stevie G

"Oracle database error 904: ORA-00904: "A6"."mn:EVENT_TS:ok": invalid identifier ORA-02063: preceding line from BIQ_Z1PC"

“Oracle 数据库错误 904：ORA-00904：“A6”。“mn:EVENT_TS:ok”：无效标识符 ORA-02063：来自 BIQ_Z1PC 的前一行”

SELECT "t0"."TEMP(Calculation_1012184020205" AS 
"TEMP(Calculation_1012184020205",
"t3"."__measure__2" AS "TEMP(Calculation_1103944862926",
"t3"."__measure__4" AS "TEMP(Calculation_1103944862921",
"t0"."TEMP(Calculation_8523062336790" AS "TEMP(Calculation_8523062336790",
"t0"."mn:EVENT_TS:ok" AS "mn:EVENT_TS:ok",
"t0"."usr:Calculation_10121840202058" AS "usr:Calculation_10121840202058",
"t0"."usr:Calculation_85230623367908" AS "usr:Calculation_85230623367908"
FROM (
SELECT TO_NUMBER(TO_CHAR(TRUNC(CAST("AQE Source Data 5.30.2018"."EVENT_TS" 
AS 
DATE)),'MM')) AS "mn:EVENT_TS:ok",
COUNT(DISTINCT (CASE WHEN ("AQE Source Data 5.30.2018"."DISPO_CD" IS NULL 
AND ("AQE Source Data 5.30.2018"."CUZ_AREA_ID" <> '0_DEFECTS') AND (NOT 
(SUBSTR("AQE Source Data 5.30.2018"."EVENT_DESC", 1, LENGTH('0')) = '0')) 
AND ("AQE Source Data 5.30.2018"."QUALITY_VELOCITY" = 'Q') AND (INSTR("AQE 
Source Data 5.30.2018"."DISC_AREA_DESC",'PDI') > 0)) THEN "AQE Source Data 
5.30.2018"."EVENT_NO" ELSE NULL END)) AS "TEMP(Calculation_1012184020205",
COUNT(DISTINCT (CASE WHEN (("400 Machines"."MIN(DISC_AREA_ID)" = '400') OR 
("400 Machines"."MIN(DISC_AREA_ID)" = '450')) THEN "400 Machines"."SER_NO" 
ELSE NULL END)) AS "TEMP(Calculation_8523062336790",
COUNT(DISTINCT (CASE WHEN ("AQE Source Data 5.30.2018"."DISPO_CD" IS NULL 
AND ("AQE Source Data 5.30.2018"."CUZ_AREA_ID" <> '0_DEFECTS') AND (NOT 
(SUBSTR("AQE Source Data 5.30.2018"."EVENT_DESC", 1, LENGTH('0')) = '0')) 
AND ("AQE Source Data 5.30.2018"."QUALITY_VELOCITY" = 'Q') AND (INSTR("AQE 
Source Data 5.30.2018"."DISC_AREA_DESC",'PDI') > 0)) THEN "AQE Source Data 
5.30.2018"."EVENT_NO" ELSE NULL END)) AS "usr:Calculation_10121840202058",
COUNT(DISTINCT (CASE WHEN (("400 Machines"."MIN(DISC_AREA_ID)" = '400') OR 
("400 Machines"."MIN(DISC_AREA_ID)" = '450')) THEN "400 Machines"."SER_NO" 
ELSE NULL END)) AS "usr:Calculation_85230623367908"
FROM (
SELECT EVENT_TS, 

EVENT_NO,FAC_PROD_FAM_CD,SER_PFX,SER_NO,CUZ_AREA_ID,CUZ_AREA_DESC,
DISC_AREA_ID,  
DISC_AREA_DESC,EVENT_DESC,QUALITY_VELOCITY,ASGN_TO,FIXER_1,PD_ID,
EVENT_CAT_ID_NO,EVENT_CID_DESC_TXT,CMPNT_SERIAL_NO,NEW_FOUND_MISSED,
MISSED_AREA_ID,RPR_MIN,WAIT_TIME,DISPO_CD,PROTOTYPE_IND,EXT_CPY_STAT,
CLSE_STAT,CLSE_TS,CAUSE_SHIFT,DEF_WELD_ INC,WELD_SEAM_ID
FROM
ABUS_DW.V_BIQ_R8_QWB_EVENTS
WHERE
(FAC_PROD_FAM_CD='ACOM' OR FAC_PROD_FAM_CD='SCOM' OR FAC_PROD_FAM_CD='LAP' 
OR FAC_PROD_FAM_CD='RM' OR FAC_PROD_FAM_CD='SCRD') 
AND (DISC_AREA_ID<>'501' AND DISC_AREA_ID<>'525' AND DISC_AREA_ID<>'600' AND 
DISC_AREA_ID<>'700' AND DISC_AREA_ID<>'701' AND DISC_AREA_ID<>'702' AND 
DISC_AREA_ID<>'703' AND DISC_AREA_ID<>'704' AND 
DISC_AREA_ID<>'705' AND DISC_AREA_ID<>'706' AND DISC_AREA_ID<>'707' AND 
DISC_AREA_ID<>'800' AND DISC_AREA_ID<>'900')
AND PROTOTYPE_IND<>'Y' AND EXT_CPY_STAT<>'D' AND
EVENT_TS>=TO_DATE('2015-10-01', 'YYYY-MM-DD')
) "AQE Source Data 5.30.2018"
LEFT JOIN (
SELECT DISTINCT
MIN(EVENT_TS), MIN(EVENT_NO), MIN(DISC_AREA_ID), MIN(DISC_AREA_DESC), 
MIN(EVENT_DESC), MIN(EXT_CPY_STAT), MIN(FAC_PROD_FAM_CD), SER_NO, 
PROTOTYPE_IND
FROM ABUS_DW.V_BIQ_R8_QWB_EVENTS
WHERE
(FAC_PROD_FAM_CD='ACOM' OR FAC_PROD_FAM_CD='SCOM' OR FAC_PROD_FAM_CD='LAP' 
OR FAC_PROD_FAM_CD='RM' OR FAC_PROD_FAM_CD='SCRD')
AND (DISC_AREA_ID='400' OR DISC_AREA_ID='450')
AND PROTOTYPE_IND<>'Y' AND EXT_CPY_STAT<>'D' AND EVENT_TS>=TO_DATE('2015-10- 
01', 'YYYY-MM-DD')
GROUP BY
SER_NO, PROTOTYPE_IND
) "400 Machines" ON ("AQE Source Data 5.30.2018"."EVENT_NO" = "400 
Machines"."MIN(EVENT_NO)")
WHERE (TO_NUMBER(TO_CHAR(TRUNC(CAST("AQE Source Data 5.30.2018"."EVENT_TS" 
AS DATE)),'YYYY')) = 2018)
GROUP BY TO_NUMBER(TO_CHAR(TRUNC(CAST("AQE Source Data 5.30.2018"."EVENT_TS" 
AS DATE)),'MM'))
) "t0"
INNER JOIN (
SELECT "t1"."mn:EVENT_TS:ok" AS "mn:EVENT_TS:ok",
SUM("t2"."__measure__1") AS "__measure__2",
SUM((CASE WHEN ("t2"."__measure__3" > 0) THEN 1 ELSE 0 END)) AS 
"__measure__4"
 FROM (
SELECT "400 Machines"."SER_NO" AS "SER_NO (Custom SQL Query)",
TO_NUMBER(TO_CHAR(TRUNC(CAST("AQE Source Data 5.30.2018"."EVENT_TS" AS 
DATE)),'MM')) AS "mn:EVENT_TS:ok"
FROM (
SELECT

EVENT_TS,EVENT_NO,FAC_PROD_FAM_CD,SER_PFX,SER_NO,CUZ_AREA_ID,
CUZ_AREA_DESC,DISC_AREA_ID,DISC_AREA_DESC,EVENT_DESC,QUALITY_VELOCITY,
ASGN_TO,FIXER_1,PD_ID,EVENT_CAT_ID_NO,
EVENT_CID_DESC_TXT,CMPNT_SERIAL_NO,NEW_FOUND_MISSED,MISSED_AREA_ID,
RPR_MIN,WAIT_TIME,DISPO_CD,PROTOTYPE_IND,EXT_CPY_STAT,CLSE_STAT,CLSE_TS,
CAUSE_SHIFT,DEF_WELD_INC,WELD_SEAM_ID
FROM
ABUS_DW.V_BIQ_R8_QWB_EVENTS
WHERE
(FAC_PROD_FAM_CD='ACOM' OR FAC_PROD_FAM_CD='SCOM' OR FAC_PROD_FAM_CD='LAP' 
OR FAC_PROD_FAM_CD='RM' OR FAC_PROD_FAM_CD='SCRD') 
AND (DISC_AREA_ID<>'501' AND DISC_AREA_ID<>'525' AND DISC_AREA_ID<>'600' AND 
DISC_AREA_ID<>'700' AND DISC_AREA_ID<>'701' AND DISC_AREA_ID<>'702' AND 
DISC_AREA_ID<>'703' AND DISC_AREA_ID<>'704' AND 
DISC_AREA_ID<>'705' AND DISC_AREA_ID<>'706' AND DISC_AREA_ID<>'707' AND 
DISC_AREA_ID<>'800' AND DISC_AREA_ID<>'900')
AND PROTOTYPE_IND<>'Y' AND EXT_CPY_STAT<>'D' AND
EVENT_TS>=TO_DATE('2015-10-01', 'YYYY-MM-DD')
) "AQE Source Data 5.30.2018"
LEFT JOIN (
SELECT DISTINCT
MIN(EVENT_TS), MIN(EVENT_NO), MIN(DISC_AREA_ID), MIN(DISC_AREA_DESC), 
MIN(EVENT_DESC), MIN(EXT_CPY_STAT), MIN(FAC_PROD_FAM_CD), SER_NO, 
PROTOTYPE_IND
FROM ABUS_DW.V_BIQ_R8_QWB_EVENTS
WHERE
(FAC_PROD_FAM_CD='ACOM' OR FAC_PROD_FAM_CD='SCOM' OR FAC_PROD_FAM_CD='LAP' 
OR FAC_PROD_FAM_CD='RM' OR FAC_PROD_FAM_CD='SCRD')
AND (DISC_AREA_ID='400' OR DISC_AREA_ID='450')
AND PROTOTYPE_IND<>'Y' AND EXT_CPY_STAT<>'D' AND EVENT_TS>=TO_DATE('2015-10- 
01', 'YYYY-MM-DD')
GROUP BY
SER_NO, PROTOTYPE_IND
) "400 Machines" ON ("AQE Source Data 5.30.2018"."EVENT_NO" = "400 
Machines"."MIN(EVENT_NO)")
WHERE (TO_NUMBER(TO_CHAR(TRUNC(CAST("AQE Source Data 5.30.2018"."EVENT_TS" 
AS DATE)),'YYYY')) = 2018)
GROUP BY "400 Machines"."SER_NO",
TO_NUMBER(TO_CHAR(TRUNC(CAST("AQE Source Data 5.30.2018"."EVENT_TS" AS 
DATE)),'MM'))
) "t1"
INNER JOIN (
SELECT "400 Machines"."SER_NO" AS "SER_NO (Custom SQL Query)",
COUNT(DISTINCT (CASE WHEN ((CASE WHEN (INSTR("AQE Source Data 
5.30.2018"."DISC_AREA_DESC",'PDI') > 0) THEN 1 WHEN (("AQE Source Data 
5.30.2018"."DISC_AREA_ID" = '500') AND ("AQE Source Data 
5.30.2018"."QUALITY_VELOCITY" = 'Q')) THEN 2 ELSE NULL END) = 1) THEN "AQE 
Source Data 5.30.2018"."SER_NO" WHEN NOT ((CASE WHEN (INSTR("AQE Source Data 
5.30.2018"."DISC_AREA_DESC",'PDI') > 0) THEN 1 WHEN (("AQE Source Data 
5.30.2018"."DISC_AREA_ID" = '500') AND ("AQE Source Data 
5.30.2018"."QUALITY_VELOCITY" = 'Q')) THEN 2 ELSE NULL END) = 1) THEN NULL 
ELSE NULL END)) AS "__measure__1",
COUNT(DISTINCT (CASE WHEN ("AQE Source Data 5.30.2018"."DISPO_CD" IS NULL 
AND ("AQE Source Data 5.30.2018"."CUZ_AREA_ID" <> '0_DEFECTS') AND (NOT 
(SUBSTR("AQE Source Data 5.30.2018"."EVENT_DESC", 1, LENGTH('0')) = '0')) 
AND ("AQE Source Data 5.30.2018"."DISC_AREA_ID" = '400')) THEN "AQE Source 
Data 5.30.2018"."EVENT_NO" ELSE NULL END)) AS "__measure__3"
FROM (
SELECT
EVENT_TS,EVENT_NO,FAC_PROD_FAM_CD,SER_PFX,SER_NO,CUZ_AREA_ID,
CUZ_AREA_DESC,DISC_AREA_ID,DISC_AREA_DESC,EVENT_DESC,QUALITY_VELOCITY,
ASGN_TO,FIXER_1,PD_ID,EVENT_CAT_ID_NO,
EVENT_CID_DESC_TXT,CMPNT_SERIAL_NO,NEW_FOUND_MISSED,MISSED_AREA_ID,
RPR_MIN,WAIT_TIME,DISPO_CD,PROTOTYPE_IND,EXT_CPY_STAT,CLSE_STAT,CLSE_TS,
CAUSE_SHIFT,DEF_WELD_INC,WELD_SEAM_ID
FROM
ABUS_DW.V_BIQ_R8_QWB_EVENTS
WHERE
(FAC_PROD_FAM_CD='ACOM' OR FAC_PROD_FAM_CD='SCOM' OR FAC_PROD_FAM_CD='LAP' 
OR FAC_PROD_FAM_CD='RM' OR FAC_PROD_FAM_CD='SCRD') 
AND (DISC_AREA_ID<>'501' AND DISC_AREA_ID<>'525' AND DISC_AREA_ID<>'600'
AND DISC_AREA_ID<>'700' AND DISC_AREA_ID<>'701' AND DISC_AREA_ID<>'702' AND 
DISC_AREA_ID<>'703' AND DISC_AREA_ID<>'704' AND 
DISC_AREA_ID<>'705' AND DISC_AREA_ID<>'706' AND DISC_AREA_ID<>'707' AND 
DISC_AREA_ID<>'800' AND DISC_AREA_ID<>'900')
AND PROTOTYPE_IND<>'Y' AND EXT_CPY_STAT<>'D' AND
EVENT_TS>=TO_DATE('2015-10-01', 'YYYY-MM-DD')
) "AQE Source Data 5.30.2018"
LEFT JOIN (
SELECT DISTINCT
MIN(EVENT_TS), MIN(EVENT_NO), MIN(DISC_AREA_ID), MIN(DISC_AREA_DESC), 
MIN(EVENT_DESC), MIN(EXT_CPY_STAT), MIN(FAC_PROD_FAM_CD), SER_NO,
PROTOTYPE_IND
FROM ABUS_DW.V_BIQ_R8_QWB_EVENTS
WHERE
(FAC_PROD_FAM_CD='ACOM' OR FAC_PROD_FAM_CD='SCOM' OR FAC_PROD_FAM_CD='LAP' 
OR FAC_PROD_FAM_CD='RM' OR FAC_PROD_FAM_CD='SCRD')
AND (DISC_AREA_ID='400' OR DISC_AREA_ID='450')
AND PROTOTYPE_IND<>'Y' AND EXT_CPY_STAT<>'D' AND EVENT_TS>=TO_DATE(
'2015-10- 01', 'YYYY-MM-DD')
GROUP BY
SER_NO, PROTOTYPE_IND
) "400 Machines" ON ("AQE Source Data 5.30.2018"."EVENT_NO" = "400 
Machines"."MIN(EVENT_NO)")
GROUP BY "400 Machines"."SER_NO"
) "t2" ON (("t1"."SER_NO (Custom SQL Query)" = "t2"."SER_NO (Custom SQL 
Query)") OR (("t1"."SER_NO (Custom SQL Query)" IS NULL) AND ("t2"."SER_NO 
(Custom SQL Query)" IS NULL)))
GROUP BY "t1"."mn:EVENT_TS:ok"
) "t3" ON (("t0"."mn:EVENT_TS:ok" = "t3"."mn:EVENT_TS:ok") OR 
(("t0"."mn:EVENT_TS:ok" IS NULL) AND ("t3"."mn:EVENT_TS:ok" IS NULL)))

回答by William Robertson

It appears to be the blank space in DEF_WELD_ INC, which should be DEF_WELD_INC. I created a table v_biq_r8_qwb_eventsto match the columns expected by subquery "AQE Source Data 5.30.2018", removed the line breaks in things like

它似乎是 中的空白区域DEF_WELD_ INC，应该是DEF_WELD_INC。我创建了一个表v_biq_r8_qwb_events来匹配子查询预期的列"AQE Source Data 5.30.2018"，删除了诸如

ON ("AQE Source Data 5.30.2018"."EVENT_NO" = "400 
Machines"."MIN(EVENT_NO)")

which I'm assuming should be

我假设应该是

ON ("AQE Source Data 5.30.2018"."EVENT_NO" = "400 Machines"."MIN(EVENT_NO)")

and that was the only error.

这是唯一的错误。

回答by Seeds

Double quoted strings are identifiers in the select, you can use them to preserve case in the AS portion, but in the select list they are identifiers. This looks like really awful machine generated sql for something other than oracle, and not complete.

双引号字符串是选择中的标识符，您可以使用它们来保留 AS 部分中的大小写，但在选择列表中它们是标识符。这看起来真的很糟糕，机器为 oracle 以外的东西生成了 sql，而且不完整。