apandey
New User
Joined: 31 Aug 2009
Posts: 73
Location: Mumbai
|
|
|
|
Hi All,
Below query is taking lots of time because of which the job is abending, giving time out reason. Pls Note: INDEX are already built on where clause predicates.
---------------------------------------------------------
EXEC SQL
DECLARE PVPWCUR CURSOR FOR
SELECT F1.I_MOD_YR
,F1.C_FAM
,F1.C_GRP
,Q1.I_VSC
,Q1.Q_PART_REQD
,Q1.Q_PART_FPV_ANNL
,X1.X_DESC
FROM
(SELECT DISTINCT(E1.I_PART)
,E1.I_MOD_YR
,E1.C_FAM
,E1.C_GRP
FROM E.EBUCREL E1
WHERE E1.C_STAGE_DVLP = '3'
AND E1.D_EFF_OUT = '9999-12-31'
AND E1.C_UM <> 'RF'
AND E1.I_MOD_YR = :EPVSDTL.I-MOD-YR
AND E1.C_FAM >= :WS-FAM-LOW
AND E1.C_FAM <= :WS-FAM-HIGH
AND E1.I_PART >= :WS-PART-LOW
AND E1.I_PART <= :WS-PART-HIGH
AND E1.C_GRP >= :WS-BCG-LOW
AND E1.C_GRP <= :WS-BCG-HIGH
) AS F1,
(
SELECT P1.I_MOD_YR
,P1.C_FAM
,P1.I_PART
,P1.I_VSC
,P1.Q_PART_REQD
,SUM(P1.Q_PART_FPV_ANNL) AS Q_PART_FPV_ANNL
FROM E.EPVSDTL P1
WHERE P1.I_PRTITN = :EPVSDTL.I-PRTITN
AND P1.I_MOD_YR = :EPVSDTL.I-MOD-YR
AND P1.C_FAM >= :WS-FAM-LOW
AND P1.C_FAM <= :WS-FAM-HIGH
AND P1.I_PART >= :WS-PART-LOW
AND P1.I_PART <= :WS-PART-HIGH
AND P1.I_VSC >= :MD-VSC-MIN
AND P1.I_VSC <= :MD-VSC-MAX
AND P1.C_LINE >= :MD-LINE-MIN
AND P1.C_LINE <= :MD-LINE-MAX
AND P1.C_MKT >= :MD-MKT-MIN
AND P1.C_MKT <= :MD-MKT-MAX
AND P1.I_PLT >= :MD-PLT-MIN
AND P1.I_PLT <= :MD-PLT-MAX
AND P1.C_SERIES >= :MD-SRS-MIN
AND P1.C_SERIES <= :MD-SRS-MAX
AND P1.C_BDY_STYLE >= :MD-BDYSTL-MIN
AND P1.C_BDY_STYLE <= :MD-BDYSTL-MAX
GROUP BY P1.I_MOD_YR
,P1.C_FAM
,P1.I_PART
,P1.I_VSC
,P1.Q_PART_REQD
) AS Q1, E.EBLDGRP X1
WHERE F1.I_PART = Q1.I_PART
AND F1.I_MOD_YR = Q1.I_MOD_YR
AND F1.C_FAM = Q1.C_FAM
AND X1.C_GRP = F1.C_GRP
ORDER BY F1.I_MOD_YR
,F1.C_FAM
,F1.C_GRP
,F1.I_PART
END-EXEC.
-------------------------------------------------------------------------- |
|
