CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
Usman YousafZai
Usman YousafZai

Reputation: 1120

UPDATING Table Column with new Entry

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

Upvotes: 9

Views: 517

Answers (2)

Alen Oblak
Alen Oblak

Reputation: 3325

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;

Upvotes: 5

Florin Ghita
Florin Ghita

Reputation: 17643

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;

Upvotes: 5

Related Questions