Re: How to do a numering by month ?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/26
Message-ID: <8e5dpg$e8a$1@nnrp1.deja.com>#1/1

In article <8e501g$v8b$1_at_nnrp1.deja.com>,   hdupre_at_my-deja.com wrote:
> I'd like to implement on the server side a numbering which resets to 0
> when the first record is created in the beginning of each month.
> Is it possible to do that with sequences or do you know other
> solutions ?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

You can try something like this:

ops$tkyte_at_8i> create table test ( x int, msg varchar2(5) ); Table created.

ops$tkyte_at_8i> create sequence my_seq;
Sequence created.

ops$tkyte_at_8i> create table first_seq_of_the_month( theMonth date primary key, seq_offset int );
Table created.

ops$tkyte_at_8i> create or replace trigger test_trigger   2 before insert on test for each row
  3 begin

  4          select my_seq.nextval - seq_offset into :new.x
  5            from first_seq_of_the_month
  6           where theMonth = trunc( sysdate, 'MONTH' );

  7
  8 exception

  9          when no_data_found then
 10                  insert into first_seq_of_the_month
 11                   values ( trunc(sysdate,'MONTH'), my_seq.nextval );
 12                  :new.x := 0;

 13 end;
 14 /
Trigger created.

ops$tkyte_at_8i> insert into test values ( 0, '1' );
ops$tkyte_at_8i> insert into test values ( 0, '1' );
ops$tkyte_at_8i> insert into test values ( 0, '1' );

ops$tkyte_at_8i> select * from test;

         X MSG
---------- -----

         0 1
         1 1
         2 1

ops$tkyte_at_8i> REM pretend the month changed ops$tkyte_at_8i> update first_seq_of_the_month set theMonth = add_months( theMonth, -1 );

1 row updated.

ops$tkyte_at_8i> insert into test values ( 0, '2' );
ops$tkyte_at_8i> insert into test values ( 0, '2' );
ops$tkyte_at_8i> insert into test values ( 0, '2' );

ops$tkyte_at_8i> select * from test;

         X MSG
---------- -----

         0 1
         1 1
         2 1
         0 2
         1 2
         2 2

6 rows selected.

ops$tkyte_at_8i> select * from first_seq_of_the_month;

THEMONTH SEQ_OFFSET

--------- ----------
01-MAR-00          1
01-APR-00          4

--
Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html

--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Apr 26 2000 - 00:00:00 CDT