oracle解析错误示例

最新推荐文章于 2025-03-21 15:41:33 发布

原创最新推荐文章于 2025-03-21 15:41:33 发布 · 471 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

数据库专栏收录该内容

34 篇文章

订阅专栏

              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

执行结果：