Re: Q:SQL:How to update pagenumbers

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 14 Oct 1998 13:50:39 GMT
Message-ID: <3624a8b5.928214@dcsun4.us.oracle.com>

On Sun, 11 Oct 1998 21:43:25 +0200, Ralf Bender <Ralf.Bender_at_wolnet.de> wrote:

>Hello *.*
>i've got a high problem in my little opinion.
>there is a table like this :
>
>company number page timestamp(MM.DD.YY)
>11 123 1 07.09.98
>11 123 2 07.09.98
>11 123 1 09.09.98
>11 123 2 09.09.98
>
>
>now, i must renumber the page of the youngest records.
>the table must look like this :
>
>company number page timestamp(MM.DD.YY)
>11 123 1 07.09.98
>11 123 2 07.09.98
>11 123 3 09.09.98
>11 123 4 09.09.98
>
>
>Is there somebody who can help me ?

Using PL/SQL you can use the procedure ...

create or replace
procedure reorder_page is

  l_idx number := 1;
  l_comp number := -1;
  l_num number := -1;

begin
  for c in ( select T1.*, rowid

               from T1
              order by company, num, timestamp )

  loop
    if l_comp <> c.company and l_num <> c.num then

      l_idx := 1;
      l_comp := c.company;
      l_num := c.num;

    end if;
    update T1 set page = l_idx where rowid = c.rowid;     l_idx := l_idx + 1;
  end loop;
  commit;
end;
/

eg.

SQL> select * from t1;

   COMPANY NUM PAGE TIMESTAMP

---------- ---------- ---------- ---------
        11        123          1 07-SEP-98
        11        123          2 07-SEP-98
        11        123          1 09-SEP-98
        11        123          2 09-SEP-98

SQL> exec reorder_page

PL/SQL procedure successfully completed.

SQL> select * from t1;

   COMPANY NUM PAGE TIMESTAMP

---------- ---------- ---------- ---------
        11        123          1 07-SEP-98
        11        123          2 07-SEP-98
        11        123          3 09-SEP-98
        11        123          4 09-SEP-98

SQL> hope this helps

chris. Received on Wed Oct 14 1998 - 08:50:39 CDT