Home » SQL & PL/SQL » SQL & PL/SQL » how to read the next record
how to read the next record [message #438889] Tue, 12 January 2010 20:54 Go to next message
kuldezak
Messages: 2
Registered: January 2010
Junior Member
I'm trying to make a procedure with a case like this :

I have a table

EMPNO START_DATE END_DATE
------- ----------- ----------
0001 20100101 20100111 data1
0001 20100120 20100130 data2
0001 20100205 data3


What i'm trying to do is updating data1.end_date with data2.start_date and data2.end_date with data3.start_date that has the same EMPNO. This process will loop until the end of the record.

The result i was hoping to achieve :

EMPNO START_DATE END_DATE
------- ----------- ----------
0001 20100101 20100120 data1
0001 20100120 20100205 data2
0001 20100205 data3

Thanks before,
Reza
Re: how to read the next record [message #438890 is a reply to message #438889] Tue, 12 January 2010 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Please realize that rows in a table are like balls in a basket.
There is NO inherent order to rows in a table.

You need to research LEAD & LAG functions.
Re: how to read the next record [message #438927 is a reply to message #438889] Wed, 13 January 2010 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a workingTest case: create table and insert statements along with the result you want with these data.

Have a look at LAG/LEAD functions.
here's an example:
SQL> select empno, hiredate, 
  2         lag(hiredate) over(order by hiredate) previous_hiredate
  3  from emp
  4  /
     EMPNO HIREDATE   PREVIOUS_H
---------- ---------- ----------
      7369 17/12/1980
      7499 20/02/1981 17/12/1980
      7521 22/02/1981 20/02/1981
      7566 02/04/1981 22/02/1981
      7698 01/05/1981 02/04/1981
      7782 09/06/1981 01/05/1981
      7844 08/09/1981 09/06/1981
      7654 28/09/1981 08/09/1981
      7839 17/11/1981 28/09/1981
      7900 03/12/1981 17/11/1981
      7902 03/12/1981 03/12/1981
      7934 23/01/1982 03/12/1981
      7788 19/04/1987 23/01/1982
      7876 23/05/1987 19/04/1987

14 rows selected.

Regards
Michel
Re: how to read the next record [message #438933 is a reply to message #438889] Wed, 13 January 2010 01:42 Go to previous message
kuldezak
Messages: 2
Registered: January 2010
Junior Member
thanks for the advice guys post guide wise and also for the LEAD & LAG function.
Previous Topic: help in PROCEDURE
Next Topic: SEQ NR Generation based on Code
Goto Forum:
  


Current Time: Sat Sep 24 21:29:02 CDT 2016

Total time taken to generate the page: 0.10041 seconds