1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

slow query issue [urgent]

Discussion in 'SQL PL/SQL' started by RIAZ, Aug 13, 2009.

  1. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    dear all
    I am working in Oracle 11i Apps, problem is

    I am trying to JOIN two tables using the following criteria

    GL_JE_LINES.GL_SL_LINK_ID = CST_AE_LINES.GL_SL_LINK_ID

    but this take too much time to execute :(

    please guide me....
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi

    can we see the full query?
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
  4. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Code (SQL):
    SELECT    gcc.segment1
           || '-'
           || gcc.segment2
           || '-'
           || gcc.segment3
           || '-'
           || gcc.segment4
           || '-'
           || gcc.segment5
           || '-'
           || gcc.segment6
           || '-'
           || gcc.segment7 AS gl_account,
           NVL (cal.accounted_dr, 0) AS ac_dr, NVL (cal.accounted_cr, 0) AS ac_cr
      FROM cst_ae_headers cah,
           cst_ae_lines cal,
           gl_code_combinations gcc,
           gl_je_lines gjl,
           gl_je_headers gjh
     WHERE gcc.segment4 BETWEEN :p_acct_from AND :p_acct_to
       AND TRUNC (gjl.effective_date) <= :p_date
       AND gcc.code_combination_id = gjl.code_combination_id
       AND gjh.je_header_id = gjl.je_header_id
       AND gjh.je_source = 'Periodic Inventory'
       AND gjl.STATUS = 'P'
       AND gjl.gl_sl_link_id = NVL (cal.gl_sl_link_id, 0)
       AND cah.ae_header_id = cal.ae_header_id
       AND cah.period_name = gjh.period_name
       AND cah.ae_category = gjh.je_category
     
  5. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi

    It maybe an issue with the between clause. Try narrowing down on the accounts range and see.
     
  6. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    huuuu... tried this ALSO but same problem :((( please help me.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then provide the query plan for this 'problem'; no one can find the issue if you don't provide the proper information.
     
  8. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    "Basically i want to perform mapping from GL to INVENTORY & PAYABLES "

    I want to get transactional details (like po number, item description). I have GL account number and i want to route from GL to get the material transactional details.
     
  9. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    RIAZ did you check the view XLA_INV_AEL_GL_V. It might have everything you need. If not, pay special attention to the create view script for this view (or it's underlying view).