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,'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

执行结果：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!