urgent-query performance

[aanchalmahant]

Hi guys, I am using the following query, bt it takes long time to give results. Please let me know if i can improve its performance somehow.

SELECT   SUM (WO_COSTS),
         SUM (DIRECT_COSTS),
         SUM (WO_CAPITALISED),
         SUM (DIRECT_CAPITALISED),
         SUM (REVENUE),
         ACCOUNT_CODE
         from (
SELECT   /*+ PARALLEL(A4, PARALLEL(A5,   */
                    SUBSTR (A4.ACCOUNT_CODE, 13, 3) ACCOUNT_CODE,
                    SUM ((CASE
                       WHEN (    SUBSTR (A4.ACCOUNT_CODE, 13, 3) >= '000'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <= '999'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '0%%'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '1%%'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '698'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '690'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '691'
                             AND A5.ACCOUNT_CODE IS NOT NULL
                            ) THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    ) WO_COSTS,
                    SUM ((CASE
                       WHEN (    SUBSTR (A4.ACCOUNT_CODE, 13, 3) >= '000'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <= '999'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '0%%'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '1%%'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '698'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '690'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '691'
                             AND A5.ACCOUNT_CODE IS NULL
                            ) THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    )DIRECT_COSTS,
                    SUM ((CASE
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) ='698' AND A5.ACCOUNT_CODE IS NOT NULL) THEN A4.TRAN_AMOUNT
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) ='690' AND A5.ACCOUNT_CODE IS NOT NULL) THEN A4.TRAN_AMOUNT
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) ='691' AND A5.ACCOUNT_CODE IS NOT NULL) THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    ) WO_CAPITALISED,
                    SUM ((CASE
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) = '698'  AND A5.ACCOUNT_CODE IS NULL ) THEN A4.TRAN_AMOUNT
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) = '690'  AND A5.ACCOUNT_CODE IS NULL ) THEN A4.TRAN_AMOUNT
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) = '691'  AND A5.ACCOUNT_CODE IS NULL ) THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    )DIRECT_CAPITALISED,
                    SUM ((CASE
                       WHEN SUBSTR (A4.ACCOUNT_CODE, 13, 3) LIKE '1%%' or
                            SUBSTR (A4.ACCOUNT_CODE, 13, 3) LIKE '0%%' THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    )REVENUE
               FROM hdwu.MSF90001 A4,
                    hdwu.MSFX9901 A5
              WHERE
                 A4.DSTRCT_CODE = A5.DSTRCT_CODE(+)
                AND A4.PROCESS_DATE = A5.PROCESS_DATE(+)
                AND A4.TRANSACTION_NO = A5.TRANSACTION_NO(+)
                AND A4.REC900_TYPE = A5.REC900_TYPE(+)
                AND A4.USERNO = A5.USERNO(+)
AND  A4.DSTRCT_CODE = 'RPDD'
                AND A4.POSTED_STATUS NOT IN ('U','N')
                AND A4.FULL_PERIOD <= #prompt('EndPeriodPrompt','string')#     
                AND SUBSTR (A4.ACCOUNT_CODE, 5, 1) = '3'
                AND A5.DSTRCT_CODE(+) = 'RPDD'
                AND A5.FULL_PERIOD(+) <= #prompt('EndPeriodPrompt','string')#     
           GROUP BY SUBSTR (A4.ACCOUNT_CODE, 13, 3)
           )
GROUP BY ACCOUNT_CODE

[MFGF]

What tool are you defining the query in? Why are you hard-coding a query like this? One of the main benefits of a reporting tool is that it codes queries for you based on what you define in the user interface...

Regards,

MF.