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!

how to add 'BALANCE' column

Discussion in 'SQL PL/SQL' started by RIAZ, Apr 8, 2009.

  1. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    hi,
    I am working with a query and successfully get the Debit side (from Table A) and Credit side (from Table B) but i am unable to make the balance column (not exist physically in any table). Any body can help me in this regard. I am using SQL and Oracle 6i Report builder.

    I want to get the following output.
    Example:
    [TABLE]Date|Debits|Credits|Balance
    01JAN|100||100
    02JAN||50|50
    03JAN||100|-50
    04JAN|200||150
    [/TABLE]
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    well share your query please, and if possible the table structure
     
  3. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Following is the exact query.
    Code (SQL):

    SELECT      rcta.TRX_DATE AS T_R_DATE, rcta.TRX_NUMBER AS T_R_NUMBER,
            SUM(rctla.EXTENDED_AMOUNT) DR, NULL AS CR
    FROM        RA_CUSTOMER_TRX_ALL rcta,
            RA_CUSTOMER_TRX_LINES_ALL rctla
    WHERE       rcta.BILL_TO_CUSTOMER_ID = :C_ID
    AND         rcta.TRX_DATE <= to_date(:TR_DATE)
    AND     rctla.CUSTOMER_TRX_ID = rcta.CUSTOMER_TRX_ID -- join
    GROUP BY    rcta.TRX_NUMBER, rcta.TRX_DATE
    UNION
    SELECT      acra.RECEIPT_DATE AS T_R_DATE,acra.RECEIPT_NUMBER AS T_R_NUMBER, NULL AS DR, acra.AMOUNT AS CR
    FROM        AR_CASH_RECEIPTS_ALL acra
    WHERE       acra.PAY_FROM_CUSTOMER = :C_ID
    AND         acra.RECEIPT_DATE <= to_date(:TR_DATE)
    AND     acra.STATUS IN ('APP','UNAPP')
    ORDER BY    T_R_DATE ASC
     
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can use Analytical function (SUM) for the same purpose.

    Code (SQL):

    SQL> SELECT DATE_D,DEBITS, CREDITS
      2  FROM DB_CR_BAL;

    DATE_D        DEBITS    CREDITS
    --------- ---------- ----------
    01-JAN-09        100
    02-JAN-09                    50
    03-JAN-09                   100
    04-JAN-09        200
    05-JAN-09                   150

    SQL>
    SQL> SELECT DATE_D,DEBITS, CREDITS,
      2         SUM(NVL(DEBITS,0)-NVL(CREDITS,0)) OVER (ORDER BY  DATE_D) BAL
      3  FROM DB_CR_BAL  ;

    DATE_D        DEBITS    CREDITS        BAL
    --------- ---------- ---------- ----------
    01-JAN-09        100                   100
    02-JAN-09                    50         50
    03-JAN-09                   100        -50
    04-JAN-09        200                   150
    05-JAN-09                   150          0

    SQL>
     
     
  5. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    but as you can see in my query, i am using UNION is it possible with UNION ?
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Yes. But you have to use the SUM Analytical function over the Union result as below.

    Code (SQL):

    SELECT T_R_DATE,T_R_NUMBER, DR, CR ,
           SUM(NVL(DR,0) - NVL(CR,0)) OVER (ORDER BYT_R_DATE,T_R_NUMBER) BALANCE
    FROM  (
            SELECT      rcta.TRX_DATE AS T_R_DATE, rcta.TRX_NUMBER AS T_R_NUMBER,
                    SUM(rctla.EXTENDED_AMOUNT) DR, NULL AS CR
            FROM        RA_CUSTOMER_TRX_ALL rcta,
                    RA_CUSTOMER_TRX_LINES_ALL rctla
            WHERE       rcta.BILL_TO_CUSTOMER_ID = :C_ID
            AND         rcta.TRX_DATE <= to_date(:TR_DATE)
            AND     rctla.CUSTOMER_TRX_ID = rcta.CUSTOMER_TRX_ID -- join
            GROUP BY    rcta.TRX_NUMBER, rcta.TRX_DATE
            UNION
            SELECT      acra.RECEIPT_DATE AS T_R_DATE,acra.RECEIPT_NUMBER AS T_R_NUMBER, NULL AS DR, acra.AMOUNT AS CR
            FROM        AR_CASH_RECEIPTS_ALL acra
            WHERE       acra.PAY_FROM_CUSTOMER = :C_ID
            AND         acra.RECEIPT_DATE <= to_date(:TR_DATE)
            AND     acra.STATUS IN ('APP','UNAPP')
           )
     
     
  7. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    It really works, thank you so much.