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!

Problem with subquery for sum data

Discussion in 'General' started by wildanmajid, Jun 16, 2015.

  1. wildanmajid

    wildanmajid Guest

    Hello guys ,
    i'm wily

    i need help for my subquery,
    when im compile, my QUERY was successful for a few record / Data
    but when i'm compile and the Record/Data is to much, i have trouble with my loading compile

    This is my QUERY
    what should I change?

    SELECT DISTINCT
    (
    (
    SELECT NVL(SUM(AIA.INVOICE_AMOUNT),0)
    FROM AP_INVOICES_ALL AIA
    ,AP_SUPPLIERS APS
    WHERE AIA.VENDOR_ID = APS.VENDOR_ID
    AND AIA.INVOICE_TYPE_LOOKUP_CODE IN ('DEBIT', 'STANDARD', 'PREPAYMENT')
    AND AIA.ORG_ID = NVL:)P_ORG_ID,AIA.ORG_ID)
    AND AIA.INVOICE_AMOUNT <> 0
    AND AIA.VENDOR_ID = NVL:)P_VENDOR_ID, AIA.VENDOR_ID)
    AND AIA.GL_DATE < :p_DATE_FROM
    )
    -
    (
    SELECT NVL(SUM(ACA.AMOUNT),0)
    FROM AP_CHECKS_ALL ACA
    ,AP_SUPPLIERS APS
    WHERE ACA.VENDOR_ID = APS.VENDOR_ID
    AND ACA.ORG_ID = NVL:)P_ORG_ID,ACA.ORG_ID)
    AND ACA.STATUS_LOOKUP_CODE <> 'VOIDED'
    AND ACA.VENDOR_ID = NVL:)P_VENDOR_ID, ACA.VENDOR_ID)
    AND ACA.CHECK_DATE < :p_DATE_FROM
    )
    -
    (
    SELECT NVL(SUM(ABS(AIL.AMOUNT)),0)
    FROM AP_INVOICE_LINES_ALL AIL
    ,AP_INVOICES_ALL AIA
    ,AP_SUPPLIERS APS
    WHERE
    AIA.INVOICE_ID = AIL.INVOICE_ID
    AND AIL.LINE_TYPE_LOOKUP_CODE IN ('PREPAY')
    AND AIL.AMOUNT <> 0
    AND AIA.VENDOR_ID = APS.VENDOR_ID
    AND AIL.ORG_ID = NVL:)P_ORG_ID,AIA.ORG_ID)
    AND AIA.VENDOR_ID = NVL:)P_VENDOR_ID, AIA.VENDOR_ID)
    AND AIL.ACCOUNTING_DATE < :p_DATE_FROM
    )
    ) BB
    FROM
    AP_INVOICES_ALL AIA
    ,AP_SUPPLIERS APS
    ,AP_CHECKS_ALL ACA
    ,AP_INVOICE_LINES_ALL AIL
    WHERE
    --INVOICE
    AIA.VENDOR_ID = APS.VENDOR_ID
    AND AIA.INVOICE_TYPE_LOOKUP_CODE IN ('DEBIT', 'STANDARD', 'PREPAYMENT')
    AND AIA.ORG_ID = NVL:)P_ORG_ID,AIA.ORG_ID)
    AND AIA.INVOICE_AMOUNT <> 0
    AND AIA.VENDOR_ID = NVL:)P_VENDOR_ID, AIA.VENDOR_ID)
    AND AIA.GL_DATE < :p_DATE_FROM
    --PAYMENT
    AND ACA.VENDOR_ID = APS.VENDOR_ID
    AND ACA.ORG_ID = NVL:)P_ORG_ID,ACA.ORG_ID)
    AND ACA.STATUS_LOOKUP_CODE <> 'VOIDED'
    AND ACA.VENDOR_ID = NVL:)P_VENDOR_ID, ACA.VENDOR_ID)
    AND ACA.CHECK_DATE < :p_DATE_FROM
    ----PREPAY
    AND AIL.LINE_TYPE_LOOKUP_CODE IN ('PREPAY')
    AND AIL.AMOUNT <> 0
    AND AIL.ORG_ID = NVL:)P_ORG_ID,AIA.ORG_ID)
    AND AIL.ACCOUNTING_DATE < :p_DATE_FROM
     
  2. Eteserneque

    Eteserneque Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    USA
    aerial drones for sale

    aerial drones for sale
     
  3. Eteserneque

    Eteserneque Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    USA
    camera drones for sale

    aerial drones for sale