sql语句因语句太长或者太复杂

select a.*, PUR_CONT_LIST_CONTRACT.PUR_CONT_VERIFY_NO from
    (SELECT  PUR_CONT_BASE_INFO.* FROM PUR_CONT_BASE_INFO PUR_CONT_BASE_INFO LEFT JOIN (
          SELECT PM.USABLE_STATUS USABLE_STATUSS, PM.PROJ_NO, PM.PUR_CONT_BASE_INFO_ID PUR_CONT_BASE_INFO_IDS FROM V_PUR_CONT_LIST_MATE_ALL PM
                      WHERE PM.USABLE_STATUS='1') M ON PUR_CONT_BASE_INFO.PUR_CONT_BASE_INFO_ID = M.PUR_CONT_BASE_INFO_IDS
                      WHERE PUR_CONT_BASE_INFO.USABLE_STATUS = '1' AND PUR_CONT_BASE_INFO.IS_HISTORY_VERSION <> '1'  AND  1=1  AND  (M.PROJ_NO = 'ZEC13BA0B00002.121001')  AND PUR_CONT_BASE_INFO_ID in (
        ( select PUR_CONT_BASE_INFO_ID from PUR_CONT_BASE_INFO q where USABLE_STATUS='1' and q.PUR_MANAGER_CODE like '109910040000000000100001000010000[1,3,4,5,6,7]%' or q.PUR_MANAGER_CODE like '1099100400000000001000010000[2,3,4,5]%' UNION ALL SELECT PUR_CONT_BASE_INFO_ID FROM PUR_CONT_BASE_INFO ) ) AND ((AUDIT_STATE='003' and CONT_STATE  IN ('001','002') )
          or (CONT_STATE  IN ('001','002','007','008','011','004')))  ) a left outer join PUR_CONT_LIST_CONTRACT on a.PUR_CONT_BASE_INFO_ID= PUR_CONT_LIST_CONTRACT.PUR_CONT_BASE_INFO_ID ORDER BY a.CREATE_DATE DESC, a.CONT_STATE asc
          此为sql语句，我在模糊查询的时候报错，提出来sql，DB2提示我 -101 54001 SQL语句超出了已确定的DB2限制：例如，表的数目太多，语句中的字节太多      ，，我到DBA上 sql语句因语句太长或者太复杂

6 个解决方案

#1

没人么

#2

建议你把in关联查询改为inner join 关联查询

#3

嗯呢我试试谢谢

#4

--有两个有问题的地方：

SELECT  a.* ,

        y.PUR_CONT_VERIFY_NO

FROM    ( SELECT    x.*

          FROM      PUR_CONT_BASE_INFO x

                    LEFT JOIN ( SELECT  PM.USABLE_STATUS USABLE_STATUSS ,

                                        PM.PROJ_NO ,

                                        PM.PUR_CONT_BASE_INFO_ID PUR_CONT_BASE_INFO_IDS

                                FROM    V_PUR_CONT_LIST_MATE_ALL PM

                                WHERE   PM.USABLE_STATUS = '1'

                              ) M ON x.PUR_CONT_BASE_INFO_ID = M.PUR_CONT_BASE_INFO_IDS

          WHERE     x.USABLE_STATUS = '1'

                    AND x.IS_HISTORY_VERSION <> '1'

                    AND 1 = 1

                    AND ( M.PROJ_NO = 'ZEC13BA0B00002.121001' )

                    AND PM.UR_CONT_BASE_INFO_ID IN ( ( SELECT PUR_CONT_BASE_INFO_ID

                                                     FROM   PUR_CONT_BASE_INFO q	--#1.UNION ALL的记录出自同一张表(这部分记录有过滤条件)

                                                     WHERE  USABLE_STATUS = '1'

                                                            AND (q.PUR_MANAGER_CODE LIKE '109910040000000000100001000010000[1,3,4,5,6,7]%'

                                                            OR q.PUR_MANAGER_CODE LIKE '1099100400000000001000010000[2,3,4,5]%')	--#2.这儿估计是少了一个括号

                                                     --UNION ALL

                                                     --SELECT PUR_CONT_BASE_INFO_ID

                                                     --FROM   PUR_CONT_BASE_INFO	--#1.UNION ALL的记录出自同一张表(这儿用IN的话,union all前部分的SQL,就可以省略了)

                                                   )

                                              )

                    AND ( ( AUDIT_STATE = '003'

                            AND CONT_STATE IN ( '001', '002' )

                          )

                          OR ( CONT_STATE IN ( '001', '002', '007', '008',

                                               '011', '004' ) )

                        )

        ) a

        LEFT OUTER JOIN PUR_CONT_LIST_CONTRACT y ON a.PUR_CONT_BASE_INFO_ID = y.PUR_CONT_BASE_INFO_ID

ORDER BY a.CREATE_DATE DESC ,

        a.CONT_STATE ASC

#5

建议楼主将经常查询的关联表做成视图

#6

这不是 MSSQLServer 啊？如果是的话这么点根本不长的。
建立把多余的空格去去掉吧。

#1

没人么

#2