oracle解析程序错误,oracle解析错误示例

SELECT SP.ID,

(SELECT MAX(ORG.ORG_CODE) KEEP(DENSE_RANK FIRST ORDER BY DECODE(ORG.ORG_TYPE, 5, 1))

FROM TOP_ORGANIZATION ORG

WHERE STATE = 1

START WITH ORG.ORG_ID = SPF.ORGANIZATION_ID

CONNECT BY ORG.ORG_ID = PRIOR ORG.PARENT_ORG_ID) BUREAUCODE

FROM SP_PD_FAULT SP, SP_PD_FAULT_REPORT SPF,

(

SELECT

DISTINCT F.ID

FROM

SP_PD_FAULT F,

SP_PD_FAULT_REPORT O,

TOP_ORGANIZATION ORG

WHERE

F.ID = O.FAULT_ID

AND F.FAULT_TYPE IN (

2

)

AND O.BUREAU_CODE = '0900'

AND F.BUREAU_CODE = '0900'

AND (

ORG.ORG_ID = O.ORGANIZATION_ID

OR ORG.ORG_ID = O.TEAM_ID

)

AND ORG.ORG_CODE LIKE '0900%'

AND O.FAULT_OCCUR_TIME BETWEEN TO_DATE('2017-01-01 00:00', 'yyyy-mm-dd hh24:mi') AND (

TO_DATE('2018-02-28 23:59', 'yyyy-mm-dd hh24:mi')

)

) FF

WHERE SP.ID = FF.ID

AND SP.ID = SPF.FAULT_ID

AND (SPF.IS_MERGE = '1' OR SPF.IS_MERGE IS NULL)

AND SPF.IS_SUB_OUTPUT_SWITCH = 1

执行结果：

由于解析错误导致第2列全部为一样的值。

修改后SQL：

只需在把第二列复杂SQL中的字段SPF.ORGANIZATION_ID显示出来，帮助oracle解析。

SELECT SP.ID,

SPF.ORGANIZATION_ID,

(SELECT MAX(ORG.ORG_CODE) KEEP(DENSE_RANK FIRST ORDER BY DECODE(ORG.ORG_TYPE, 5, 1))

FROM TOP_ORGANIZATION ORG

WHERE STATE = 1

START WITH ORG.ORG_ID = SPF.ORGANIZATION_ID

CONNECT BY ORG.ORG_ID = PRIOR ORG.PARENT_ORG_ID) BUREAUCODE

FROM SP_PD_FAULT SP, SP_PD_FAULT_REPORT SPF,

(

SELECT

DISTINCT F.ID

FROM

SP_PD_FAULT F,

SP_PD_FAULT_REPORT O,

TOP_ORGANIZATION ORG

WHERE

F.ID = O.FAULT_ID

AND F.FAULT_TYPE IN (

2

)

AND O.BUREAU_CODE = '0900'

AND F.BUREAU_CODE = '0900'

AND (

ORG.ORG_ID = O.ORGANIZATION_ID

OR ORG.ORG_ID = O.TEAM_ID

)

AND ORG.ORG_CODE LIKE '0900%'

AND O.FAULT_OCCUR_TIME BETWEEN TO_DATE('2017-01-01 00:00', 'yyyy-mm-dd hh24:mi') AND (

TO_DATE('2018-02-28 23:59', 'yyyy-mm-dd hh24:mi')

)

) FF

WHERE SP.ID = FF.ID

AND SP.ID = SPF.FAULT_ID

AND (SPF.IS_MERGE = '1' OR SPF.IS_MERGE IS NULL)

AND SPF.IS_SUB_OUTPUT_SWITCH = 1

执行结果：