Home » SQL & PL/SQL » SQL & PL/SQL » start previous col with 0 (ORACLE 10 G)
start previous col with 0 [message #568455] Thu, 11 October 2012 23:25 Go to next message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
Hi All,
create table test_bf_af ( e_id number, e_amt number(17,2));

INSERT INTO TEST_BF_AF ( E_ID, E_AMT ) VALUES ( 
1, 16880); 
INSERT INTO TEST_BF_AF ( E_ID, E_AMT ) VALUES ( 
2, 2500); 
INSERT INTO TEST_BF_AF ( E_ID, E_AMT ) VALUES ( 
3, 4100); 
INSERT INTO TEST_BF_AF ( E_ID, E_AMT ) VALUES ( 
4, 2500); 
INSERT INTO TEST_BF_AF ( E_ID, E_AMT ) VALUES ( 
5, 18480); 
INSERT INTO TEST_BF_AF ( E_ID, E_AMT ) VALUES ( 
6, 2500); 
INSERT INTO TEST_BF_AF ( E_ID, E_AMT ) VALUES ( 
7, 5600); 
INSERT INTO TEST_BF_AF ( E_ID, E_AMT ) VALUES ( 
8, 2500); 
INSERT INTO TEST_BF_AF ( E_ID, E_AMT ) VALUES ( 
9, 7650); 
COMMIT;

I need to list the above data as 

e_id	       e_amt		e_amt
	       before		after

1	       0		16880
2	       16880		2500
3	       2500		4100
4	       4100		2500
5	       2500		18480
6	       18480		2500
7	       2500		5600
8	       5600		2500
9	       2500		7650

Regards

Krish

[EDITED by LF: applied [code] tags]

[Updated on: Fri, 12 October 2012 00:07] by Moderator

Report message to a moderator

Re: start previous col with 0 [message #568457 is a reply to message #568455] Fri, 12 October 2012 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 19521
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
select 
  e_id,
  nvl(lag(e_amt) over (order by e_id), 0) amt_before,
  e_amt amt_after
from test_bf_af 
Re: start previous col with 0 [message #568458 is a reply to message #568457] Fri, 12 October 2012 00:13 Go to previous messageGo to next message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
hi lie,

superb

thanks
krish
Re: start previous col with 0 [message #568464 is a reply to message #568458] Fri, 12 October 2012 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58933
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
or

select 
  e_id,
  lag(e_amt, 1, 0) over (order by e_id) amt_before,
  e_amt amt_after


Regards
Michel
Re: start previous col with 0 [message #568500 is a reply to message #568457] Fri, 12 October 2012 08:16 Go to previous message
Solomon Yakobson
Messages: 2005
Registered: January 2010
Senior Member
Littlefoot wrote on Fri, 12 October 2012 01:06

nvl(lag(e_amt) over (order by e_id), 0) amt_before,


LAG has 3 parameters, so use them:

lag(e_amt,1,0) over (order by e_id) amt_before,


SY.
Previous Topic: how to run this query correctly?
Next Topic: How to insert value in column which has date datatype?
Goto Forum:
  


Current Time: Thu Aug 28 18:30:10 CDT 2014

Total time taken to generate the page: 0.09466 seconds