UPDATING Table Column with new Entry

Question

I am working on Oracle APEX. I want my report to show me the updated Debit value in Total amount column.The Report Query is shown in the following diagram mentioned below.

TABLE  "VENDOR_ACCOUNT" 
(  "VEN_ACCOUNTID"      NVARCHAR2(10), 
   "VEN_REGNO"          NVARCHAR2(10), 
   "VEN_TXDATE"         DATE, 
   "VEN_INVOICE_REFNO"  NVARCHAR2(10), 
   "TOTALAMOUNT"        NVARCHAR2(10), 
   "IN"                 NUMBER(10,0), 
   "OUT"                NUMBER(10,0) 
) 

SELECT "VEN_ACCOUNTID" , 
       "VEN_REGNO" , 
       "VEN_TXDATE" , 
       "VEN_INVOICE_REFNO" , 
       "TOTALAMOUNT" , 
       "IN",
       "OUT",   
       "TOTALAMOUNT"+"IN" as "CREDIT",
       "TOTALAMOUNT"-"OUT" as "DEBIT"
FROM Vendor_Account;

Required Scenario: I want to update the TotalAmount Column with Debit and Credit. The new Debit or Credit value has to be shown in the TotalBalance column on the next record. enter image description here

score 5 · Answer 1 · answered Feb 07 '13 at 10:19

Select
    "VEN_ACCOUNTID" , 
    "VEN_REGNO" , 
    "VEN_TXDATE" , 
    "VEN_INVOICE_REFNO" , 
    "TOTALAMOUNT" AS "OLD_TOTALAMOUNT"
    "TOTALAMOUNT" + nvl(lag (nvl("IN",0)-nvl("OUT",0)) 
                             over (partition by "VEN_REGNO" order by "VEN_ACCOUNTID" 
                             )
                        ,0) AS "TOTALAMOUNT"
    "IN",
    "OUT",  
 from Vendor_Account;

score 5 · Accepted Answer · answered Feb 07 '13 at 10:21

5

You can use Oracle analyitic function LAG:

Select "VEN_ACCOUNTID" , 
        "VEN_REGNO" , 
        "VEN_TXDATE" , 
        "VEN_INVOICE_REFNO" , 
        "TOTALAMOUNT" , 
        "TOTALAMOUNT" + lag("IN",1) over (ORDER BY "VEN_ACCOUNTID")
                      - lag("OUT",1) over (ORDER BY "VEN_ACCOUNTID") AS "NEW_TOTALAMOUNT",
        "IN",
        "OUT",  
        "TOTALAMOUNT"+"IN" as "CREDIT",
        "TOTALAMOUNT"-"OUT" as "DEBIT"
FROM    Vendor_Account;

answered Feb 07 '13 at 10:21

Alen Oblak

3,285
13
27

@alen.. It is still giving me the same answer as i mentioned it in the diagram above.. no changes are being made ! – Usman YousafZai Feb 08 '13 at 04:51
Use the LAG function. Play around with it and see what it does. As I wrote the query, you should get a new column "new total" in the APEX report. – Alen Oblak Feb 08 '13 at 07:37
@MuhammadUsman what exactly is not working then? When i take your resultset from your question and run Alen's or Florin's query on it, it gives me what you ask for: totalamount + in of previous record - out of previous record. So is it your report that "does not work" or is the query incomplete? If it is, then your question did not provide enough detail. – Tom Feb 15 '13 at 13:34
Is the problem that you're expecting the table to be updated afterwards? If so Alens query alone will not do this - it will simply return the values required per run. You'd need to modify the query (combine it with an update for example) to make the changes stick – Sean Landsman Feb 15 '13 at 20:16
@Tom .. Here is the problem "ORA-00923: FROM keyword not found where expected". I tried to play around this query but still not works..and you can easily understand the problem.. it is well explained with diagrams. I want to Update the TOTAL AMOUNT Column should be updated in next entry if some changes are being made in DEBIT or CREDIT Column. Thankyou – Usman YousafZai Feb 16 '13 at 04:55

UPDATING Table Column with new Entry

2 Answers2