how to add 'BALANCE' column

[RIAZ]

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:

Date	Debits	Credits	Balance
01JAN	100		100
02JAN		50	50
03JAN		100	-50
04JAN	200		150

[Sadik]

well share your query please, and if possible the table structure

[RIAZ]

Following is the exact query.

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

[rajavu]

You can use Analytical function (SUM) for the same purpose.

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>

[RIAZ]

but as you can see in my query, i am using UNION is it possible with UNION ?

[rajavu]

Yes. But you have to use the SUM Analytical function over the Union result as below.

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') 
       )

[RIAZ]

It really works, thank you so much.