[sqr-users] Query to SQR

[Raghvendra Dharwada <dharwada@gmail.com>]

Hi All,

I am trying to convert the following long running query (I would
rather say, it times out when I run) to an SQR. Can anyone suggest me
how to tune this query ? Also, if I incorporate the same query in an
SQR, how do I split it? I am thinking of putting all the subqueries
into different procedures and use respective variables. Is it the
right way of splitting it? Please suggest me different ways of doing
it or tuning the query. I appreciate your help.

SELECT :1 , :2 , 'XXGL1234', A.BUSINESS_UNIT, A.LEDGER, A.ACCOUNT,
A.DEPTID, A.AFFILIATE, SUM( A.POSTED_TOTAL_AMT)
  FROM PS_LEDGER A
  WHERE A.ACCOUNT = (SELECT B.ACCOUNT
  FROM PS_GL_ACCOUNT_TBL B, PSTREELEAF C, PSTREENODE D
  WHERE B.ACCOUNT = A.ACCOUNT
     AND B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.ACCOUNT = B_ED.ACCOUNT
          AND B_ED.EFFDT <= SYSDATE)
     AND B.ACCOUNT BETWEEN C.RANGE_FROM AND C.RANGE_TO
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PSTREELEAF C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.SETCNTRLVALUE = C_ED.SETCNTRLVALUE
          AND C.TREE_NAME = C_ED.TREE_NAME
          AND C_ED.EFFDT <= SYSDATE)
     AND C.TREE_NAME = 'ACCOUNT_ROLLUP'
     AND C.SETID = D.SETID
     AND C.TREE_NAME = D.TREE_NAME
     AND C.TREE_NODE_NUM = D.TREE_NODE_NUM
     AND D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PSTREENODE D_ED
        WHERE D.SETID = D_ED.SETID
          AND D.SETCNTRLVALUE = D_ED.SETCNTRLVALUE
          AND D.TREE_NAME = D_ED.TREE_NAME
          AND D_ED.EFFDT <= SYSDATE)
     AND D.TREE_NODE IN ('PROFITLOSS','BALSHEET')
     AND B.SETID = 'MODEL'
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PSTREELEAF C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.SETCNTRLVALUE = C_ED.SETCNTRLVALUE
          AND C.TREE_NAME = C_ED.TREE_NAME
          AND C_ED.EFFDT <= D.EFFDT))
     AND A.FISCAL_YEAR = :1
     AND A.ACCOUNTING_PERIOD BETWEEN 0 AND :2
     AND A.LEDGER = 'ACTUALS'
  GROUP BY  :1 ,  :2 ,  'XXGL1234',  A.BUSINESS_UNIT,  A.LEDGER, 
A.ACCOUNT,  A.DEPTID,  A.AFFILIATE

Thanks,
Raghav

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users