OraFAQ Forum: SQL & PL/SQL » how to read the next record

Home » SQL & PL/SQL » SQL & PL/SQL » how to read the next record

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to read the next record [message #438889]

Tue, 12 January 2010 20:54

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

Report message to a moderator

Re: how to read the next record [message #438890 is a reply to message #438889]

Tue, 12 January 2010 21:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: how to read the next record [message #438927 is a reply to message #438889]

Wed, 13 January 2010 01:31

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: how to read the next record [message #438933 is a reply to message #438889]

Wed, 13 January 2010 01:42

kuldezak
Messages: 2
Registered: January 2010

Junior Member

thanks for the advice guys post guide wise and also for the LEAD & LAG function.

Report message to a moderator

Previous Topic:	help in PROCEDURE
Next Topic:	SEQ NR Generation based on Code

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 09 11:49:08 CDT 2025