Home » SQL & PL/SQL » SQL & PL/SQL » running balance query (10g, win8.1)
running balance query [message #650083] Thu, 14 April 2016 12:15 Go to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
I have a table. Column BILL contains amount receivable and column RECEIVED contains the amount received.


create table t1
(
customerid number(4),
transaction_date DATE,
bill number(10),
received number(10)
);


insert into t1 values(1,'10-APR-2016',null,100000);
insert into t1 values(1,'10-APR-2016',null,200000);
insert into t1 values(1,'11-APR-2016',50000,null);
insert into t1 values(1,'12-APR-2016',null,100000);
insert into t1 values(1,'13-APR-2016',4000,null);
insert into t1 values(1,'13-APR-2016',null,20000);
insert into t1 values(1,'14-APR-2016',8000,null);


The data is 

SQL> select * from t1;

CUSTOMERID TRANSACTI      BILL  RECEIVED
---------- --------- --------- ---------
         1 10-APR-16              100000
         1 10-APR-16              200000
         1 11-APR-16     50000
         1 12-APR-16              100000
         1 13-APR-16      4000
         1 13-APR-16               20000
         1 14-APR-16      8000

7 rows selected.




My question is: how can I show a running_balance column in the query
like below


CUSTOMERID TRANSACTI      BILL  RECEIVED	running_balance
---------- --------- --------- ---------	---------
         1 10-APR-16              100000	100000
         1 10-APR-16              200000	300000
         1 11-APR-16     50000			250000
         1 12-APR-16              100000	350000
         1 13-APR-16      4000			346000	
         1 13-APR-16               20000	366000
         1 14-APR-16      8000			358000








Please.

Thanks
rzkhan



Re: running balance query [message #650084 is a reply to message #650083] Thu, 14 April 2016 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just use SUM in its analytical form:
SQL> select customerid, transaction_date, bill, received,
  2         sum(nvl(received,0)-nvl(bill,0)) over
  3           (partition by customerid order by transaction_date, rowid)
  4           balance
  5  from t1
  6  order by customerid, transaction_date, rowid
  7  /
CUSTOMERID TRANSACTION       BILL   RECEIVED    BALANCE
---------- ----------- ---------- ---------- ----------
         1 10-APR-2016                100000     100000
         1 10-APR-2016                200000     300000
         1 11-APR-2016      50000                250000
         1 12-APR-2016                100000     350000
         1 13-APR-2016       4000                346000
         1 13-APR-2016                 20000     366000
         1 14-APR-2016       8000                358000

7 rows selected.


Note that '10-APR-2016' is not a date it is a string, the proof if I try to convert it to a date:
SQL> select to_date('10-APR-2016') from dual;
select to_date('10-APR-2016') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Re: running balance query [message #650086 is a reply to message #650083] Thu, 14 April 2016 12:49 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
I think you have a data normalization problem. I would use either two tables (one for bills and another for receipts) or one table with a single AMOUNT column and a column with a flag to state whether the row is a bill or a receipt.
Re: running balance query [message #650091 is a reply to message #650084] Fri, 15 April 2016 00:16 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
Thanks Michel.

and as for normalization. actually I have two different tables which are used for bill and receipts. And the query gets the data in the same format as I have given in the table.

I also have to join some other tables for different data. But my main problem was the running sum which has been very kindly explained by Michel.

Thanks a lot ..

Best wishes

[Updated on: Fri, 15 April 2016 00:18]

Report message to a moderator

Re: running balance query [message #650139 is a reply to message #650091] Sun, 17 April 2016 04:13 Go to previous messageGo to next message
live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India
Member
<quote>
Note that '10-APR-2016' is not a date it is a string, the proof if I try to convert it to a date:
</quote>


<code>
SQL> select to_date('10-APR-2016') from dual;

select to_date('10-APR-2016') from dual

ORA-01858: a non-numeric character was found where a numeric was expected

SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered

SQL> select to_date('10-APR-2016') from dual;

TO_DATE('10-APR-2016')
----------------------
4/10/2016

SQL>
</code>
Re: running balance query [message #650140 is a reply to message #650139] Sun, 17 April 2016 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not more:
SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

SQL> select to_date('10-APR-2016') from dual;
select to_date('10-APR-2016') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

Re: running balance query [message #650142 is a reply to message #650140] Sun, 17 April 2016 04:33 Go to previous messageGo to next message
live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India
Member
why the different behavior ?
Re: running balance query [message #650143 is a reply to message #650142] Sun, 17 April 2016 04:33 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
Michel lives in France.
Re: running balance query [message #650144 is a reply to message #650143] Sun, 17 April 2016 04:44 Go to previous messageGo to next message
live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India
Member
Got you. thanks!! May be making same language and territory ,it behave similar. Smile

SQL> alter session set NLS_LANGUAGE ='FRENCH';
 
Session altered
 
SQL> alter session set NLS_TERRITORY ='FRANCE';
 
Session altered
 
SQL> alter session set nls_date_format='DD-MON-YYYY';
 
Session altered
 
SQL> select to_date('10-APR-2016') from dual;
 
select to_date('10-APR-2016') from dual
 
ORA-01843: ce n'est pas un mois valide
 
SQL> 
Re: running balance query [message #650145 is a reply to message #650144] Sun, 17 April 2016 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I only have the NLS_DATE_LANGUAGE in French; my NLS_LANGUAGE is AMERICAN to get Oracle message in English as you can see above.

[Updated on: Sun, 17 April 2016 04:50]

Report message to a moderator

Re: running balance query [message #650146 is a reply to message #650144] Sun, 17 April 2016 04:52 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
Oracle is American, and sometimes forgets that the rest of the world exists. My pet hate is the Scheduler maintenance windows: all day at the weekend. Larry doesn't realize that the weekend is not Saturday/Sunday for zillions of people.
Re: running balance query [message #650147 is a reply to message #650146] Sun, 17 April 2016 07:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2858
Registered: January 2010
Location: Connecticut, USA
Senior Member
I agree, there are many things missed out in globalization. Scheduler maintenance window is one of them. Oracle could create it based on NLS_LANG / NLS_TERRITORY, but ... On other hand, one would change it anyway in most cases. Most of us have no luxury to have whole weekend for maintenance anyway Sad .

SY.
Re: running balance query [message #650177 is a reply to message #650147] Mon, 18 April 2016 12:47 Go to previous messageGo to next message
Bill B
Messages: 1868
Registered: December 2004
Senior Member
Rather then changing the nls_date_format, always use a mask in your to_date. For example to always convert a stored date string using the american format of DD-MON-YYYY use

SELECT TO_DATE('10-APR-2016','DD-MON-YYYY','NLS_DATE_LANGUAGE = American') FROM DUAL;

It will work no matter where the code is run in any language as long as the varchar2 string is always formatted the same. That being said, a date should ALWAYS be stored in a date column!!

[Updated on: Mon, 18 April 2016 12:48]

Report message to a moderator

Re: running balance query [message #650180 is a reply to message #650177] Mon, 18 April 2016 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The original point was: do not use the following to insert into a DATE field:
insert into t1 values(1,'10-APR-2016',null,100000);


Re: running balance query [message #650217 is a reply to message #650180] Tue, 19 April 2016 13:13 Go to previous messageGo to next message
Bill B
Messages: 1868
Registered: December 2004
Senior Member
Michel Cadot wrote on Mon, 18 April 2016 13:58

The original point was: do not use the following to insert into a DATE field:
insert into t1 values(1,'10-APR-2016',null,100000);



Your right, sorry Michel. But they should still always use to_date and a format mask when inserting into a date column when using a string.
Re: running balance query [message #650218 is a reply to message #650217] Tue, 19 April 2016 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sure, and better avoid the names and use the numbers to prevent from language issues. Smile

Re: running balance query [message #653787 is a reply to message #650218] Tue, 19 July 2016 01:34 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
I am worried about the last 0 in the balance column

AG@orcl:>ed
Wrote file afiedt.buf

  1   select customerid, transactiondate, nvl(sum_sale,0) as bill,nvl(sum_receipts,0) as received,
  2             sum(nvl(sum_sale,0)-nvl(sum_receipts,0)) over
  3               (partition by customerid order by transactiondate, rowid)
  4               balance
  5      from customer_ledger
  6*     order by customerid, transactiondate, rowid
AG@orcl:>/

CUSTOMERID TRANSACTI       BILL   RECEIVED    BALANCE
---------- --------- ---------- ---------- ----------
       443 31-DEC-15       5500          0       5500
       443 14-FEB-16       3440       2500       6440
       443 15-FEB-16          0       3440       3000
       443 22-FEB-16       3660       3660       3000
       443 03-MAR-16          0       2000       1000
       443 05-MAR-16       4100          0       5100
       443 06-MAR-16          0       1500       3600
       443 14-MAR-16       2010       2100       3510
       443 20-MAR-16       3280       1510       5280
       443 21-MAR-16          0       2280       3000
       443 26-MAR-16          0       2000       1000
       443 27-MAR-16       5792          0       6792
       443 28-MAR-16          0       2790       4002
       443 02-APR-16          0       1500       2502
       443 03-APR-16       4260       2260       4502
       443 10-APR-16       2660          0       7162
       443 11-APR-16          0       2660       4502
       443 16-APR-16          0       2000       2502
       443 24-APR-16          0       1000       1502
       443 05-MAY-16        900       2402          0

20 rows selected.

Any idea please?

Re: running balance query [message #653788 is a reply to message #653787] Tue, 19 July 2016 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The balance is not bill-received but received-bill.

[Updated on: Tue, 19 July 2016 01:38]

Report message to a moderator

Re: running balance query [message #653790 is a reply to message #653788] Tue, 19 July 2016 02:08 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
It wont solve the 0 problem in the last record still..
AG@orcl:>ed
Wrote file afiedt.buf

  1   select customerid, transactiondate, nvl(sum_sale,0) as bill,nvl(sum_receipts,0) as received,
  2             sum(nvl(sum_receipts,0)-nvl(sum_sale,0)) over
  3               (partition by customerid order by transactiondate, rowid)
  4               balance
  5      from customer_ledger
  6*     order by customerid, transactiondate, rowid
AG@orcl:>/

CUSTOMERID TRANSACTI       BILL   RECEIVED    BALANCE
---------- --------- ---------- ---------- ----------
       443 31-DEC-15       5500          0      -5500
       443 14-FEB-16       3440       2500      -6440
       443 15-FEB-16          0       3440      -3000
       443 22-FEB-16       3660       3660      -3000
       443 03-MAR-16          0       2000      -1000
       443 05-MAR-16       4100          0      -5100
       443 06-MAR-16          0       1500      -3600
       443 14-MAR-16       2010       2100      -3510
       443 20-MAR-16       3280       1510      -5280
       443 21-MAR-16          0       2280      -3000
       443 26-MAR-16          0       2000      -1000
       443 27-MAR-16       5792          0      -6792
       443 28-MAR-16          0       2790      -4002
       443 02-APR-16          0       1500      -2502
       443 03-APR-16       4260       2260      -4502
       443 10-APR-16       2660          0      -7162
       443 11-APR-16          0       2660      -4502
       443 16-APR-16          0       2000      -2502
       443 24-APR-16          0       1000      -1502
       443 05-MAY-16        900       2402          0

20 rows selected.

Re: running balance query [message #653791 is a reply to message #653790] Tue, 19 July 2016 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What 0 problem?
I don't see any problem in this 0!
It is the correct value.

Re: running balance query [message #653793 is a reply to message #653791] Tue, 19 July 2016 02:19 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
oohhh.. my God...sorry..

Thanks
Re: running balance query [message #653850 is a reply to message #653793] Wed, 20 July 2016 07:19 Go to previous messageGo to next message
SheliaWilson
Messages: 1
Registered: July 2016
Location: 796 Frank Avenue Sprin...
Junior Member
Thank you for sharing such useful commands.
Re: running balance query [message #675254 is a reply to message #653850] Tue, 19 March 2019 00:13 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
If there r more than one records per date then the query results are incorrect. Cant post the result right now. Any help plz?
Re: running balance query [message #675255 is a reply to message #675254] Tue, 19 March 2019 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How is it incorrect?
There are ALREADY are several rows per date in your original test case.
Post a new VALID test case showing the problem and post the result you want with it.

Re: running balance query [message #675632 is a reply to message #675255] Thu, 11 April 2019 00:05 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
The query throws an error if a VIEW is used instead of TABLE.
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

Is there any alternative to use view and get same result?

Thanks
Re: running balance query [message #675633 is a reply to message #675632] Thu, 11 April 2019 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 19 March 2019 06:40

How is it incorrect?
There are ALREADY are several rows per date in your original test case.
Post a new VALID test case showing the problem and post the result you want with it.
Re: running balance query [message #675634 is a reply to message #675633] Thu, 11 April 2019 01:09 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
Test Case.

Three tables. CUSTOMERS, INVOICE and AMT_RCD

One View. example_view1





create table customers(
customerid number(5),
customername varchar2(60),
opening_balance number(7)
);

insert into customers values(1,'Customer One',200);


drop table invoice;

create table invoice(
customerid number(5),
empid number(5),
orderid number(5),
orderdate date,
productid number(5),
net_amount number(7)
);



insert into invoice values(1,1,1,SYSDATE,1,100);
insert into invoice values(1,1,1,SYSDATE,2,150);
insert into invoice values(1,1,3,SYSDATE,2,50);

drop table amt_rcd;

create table amt_rcd (
customerid number(5),
empid number(5),
trans_date DATE,
received number(7)
);

insert into amt_rcd values (1,1,SYSDATE-1,30);
insert into amt_rcd values (1,1,SYSDATE,20);

create view example_view1 as
select customerid,'Opening Balance' as narration, null as empid,null as orderid,null as trans_date,null as productid,opening_balance as net_amount, 0 as received from customers
where customerid=1
UNION ALL
select customerid,'Sale' as narration,empid,orderid,orderdate as trans_date,productid,net_amount as net_amount, 0 as received from invoice inv
UNION ALL
select customerid,'Payment' as narration,empid,null as orderid,trans_date as trans_date,null as productid,null as net_amount,received as recevied
from amt_rcd;




The query shows following error.

AG@orcl:>select customerid, trans_date, nvl(net_amount,0) as bill,nvl(received,0) as received,
  2                 sum(nvl(received,0)-nvl(net_amount,0)) over
  3                   (partition by customerid order by trans_date, rowid)
  4                   balance
  5         from example_view1
  6         order by customerid, trans_date, rowid
  7  /
                 (partition by customerid order by trans_date, rowid)
                                                               *
ERROR at line 3:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.


AG@orcl:>




[Updated on: Thu, 11 April 2019 01:10]

Report message to a moderator

Re: running balance query [message #675641 is a reply to message #675634] Thu, 11 April 2019 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 11 April 2019 07:31
Michel Cadot wrote on Tue, 19 March 2019 06:40

How is it incorrect?
There are ALREADY are several rows per date in your original test case.
Post a new VALID test case showing the problem nd post the result you want with it.
And I add: AND EXPLAIN IT.

And post your Oracle version: select * from v$version;

Re: running balance query [message #675647 is a reply to message #675641] Thu, 11 April 2019 03:47 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
AG@orcl:>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


And I want my result to be like below.


CUSTOMERID	NARRATION	EMPID	ORDERID	TRANS_DATE	PRODUCTID	NET_AMOUNT	RECEIVED	Balance
1		Opening Balance							200		0	        200
1		Payment		1		10-Apr-19					30	        170
1		Sale		1	1	11-Apr-19	1		100		0		270
1		Sale		1	1	11-Apr-19	2		150		0		420
1		Sale		1	3	11-Apr-19	2		50		0		470
1		Payment		1		11-Apr-19					20	        450


Re: running balance query [message #675650 is a reply to message #675647] Thu, 11 April 2019 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You missed to specify your result.
From your posted query and result:
SQL> select customerid, narration, empid, orderid, trans_date, productid,
  2         nvl(net_amount,0) as net_amount, nvl(received,0) as received,
  3         sum(nvl(net_amount,0)-nvl(received,0))
  4           over(partition by customerid order by trans_date nulls first, orderid, productid)
  5           balance
  6  from example_view1
  7  order by customerid, trans_date nulls first, orderid, productid
  8  /
CUSTOMERID NARRATION            EMPID    ORDERID TRANS_DATE           PRODUCTID NET_AMOUNT   RECEIVED    BALANCE
---------- --------------- ---------- ---------- ------------------- ---------- ---------- ---------- ----------
         1 Opening Balance                                                             200          0        200
         1 Payment                  1            10/04/2019 11:08:13                     0         30        170
         1 Sale                     1          1 11/04/2019 11:08:06          1        100          0        270
         1 Sale                     1          1 11/04/2019 11:08:06          2        150          0        420
         1 Sale                     1          3 11/04/2019 11:08:06          2         50          0        470
         1 Payment                  1            11/04/2019 11:08:13                     0         20        450

6 rows selected.

Re: running balance query [message #675663 is a reply to message #675650] Thu, 11 April 2019 05:10 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
I am really thankful to you for your help. It is what I required.

Riaz
Re: running balance query [message #675683 is a reply to message #675663] Fri, 12 April 2019 01:08 Go to previous messageGo to next message
rzkhan
Messages: 369
Registered: March 2005
Senior Member
My one query is:

AG@orcl:>;
  1* select * from vw_cledger_taj where customerid=762
AG@orcl:>/

CUSTOMERID NARRATION            EMPID    ORDERID TRANS_DAT  PRODUCTID NET_AMOUNT   RECEIVED
---------- --------------- ---------- ---------- --------- ---------- ---------- ----------
       762 Opening Balance                                                   500          0
       762 Sale                     5          3 09-APR-19          1     300000          0
       762 Sale                     9          6 12-APR-19          2     500000          0
       762 Sale                     9          6 12-APR-19          2         33          0
       762 Payment                  2            12-APR-19                              300


and when I run to get running balance

AG@orcl:>select j.trans_date,j.customerid,j.narration,j.productid,j.net_amount,j.received ,
  2  sum(nvl(net_amount,0)-nvl(received,0)) 
  3  over(partition by customerid order by trans_date nulls first, orderid) as balance
  4  from vw_cledger_taj j
  5  where customerid=762
  6  order by trans_date nulls first, orderid;

TRANS_DAT CUSTOMERID NARRATION        PRODUCTID NET_AMOUNT   RECEIVED    BALANCE
--------- ---------- --------------- ---------- ---------- ---------- ----------
                 762 Opening Balance                   500          0        500
09-APR-19        762 Sale                     1     300000          0     300500
12-APR-19        762 Sale                     2     500000          0     800533
12-APR-19        762 Sale                     2         33          0     800533
12-APR-19        762 Payment                                      300     800233


There is an error in

12-APR-19        762 Sale                     2     500000          0     800533
12-APR-19        762 Sale                     2         33          0     800533

It shows 800533 for 3rd and 4th record. While it should be 80500 and 80533 respectively.
Can anyone see where is the problem please?


Re: running balance query [message #675685 is a reply to message #675683] Fri, 12 April 2019 01:26 Go to previous message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As always you should post a test case.

Hint: add "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" in OVER clause.

Previous Topic: Import XML file into XMLType Column
Next Topic: Explain plan attached
Goto Forum:
  


Current Time: Mon Jun 17 14:15:30 CDT 2019