Home » SQL & PL/SQL » SQL & PL/SQL » reset auto generate from 1 when academic year changes (oracle 9i)
reset auto generate from 1 when academic year changes [message #351450] Tue, 30 September 2008 21:33 Go to next message
rajuakula
Messages: 63
Registered: March 2005
Member


Hi,

My academic year starts from December 1st to November 30th.
From dec 1st my comid should start from 1 and increase by 1 when new record is inserted, till nov 30th.Again from next dec 1st it should start from 1 and increment asusual.
plz. look at the data below

table1

comid       
------------------
1           
2
3
.
.
150
1
2
.
.
178
1
2
.
.

thanks,


[Updated on: Wed, 01 October 2008 00:53] by Moderator

Report message to a moderator

Re: reset auto generate from 1 when academic year changes [message #351453 is a reply to message #351450] Tue, 30 September 2008 22:44 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You should study sequences.And in that cycle keyword.

Regards,
Rajat

Re: reset auto generate from 1 when academic year changes [message #351455 is a reply to message #351450] Tue, 30 September 2008 23:18 Go to previous messageGo to next message
rajuakula
Messages: 63
Registered: March 2005
Member
Hi,
Actually my comid is varchar datatype, I am picking the last character of the data which is numeric and then auto incrementing using max,substring functions.Now how should I do this according to my requirement?

thanks
Re: reset auto generate from 1 when academic year changes [message #351456 is a reply to message #351455] Tue, 30 September 2008 23:22 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Concatenate sequence value with the varchar value.

  CREATE SEQUENCE my_seq START WITH 1  MAXVALUE 4 CACHE 2 CYCLE;

  select 'RAJAT'||myseq.nextval from dual;


Regards,
Rajat

[Updated on: Tue, 30 September 2008 23:25]

Report message to a moderator

Re: reset auto generate from 1 when academic year changes [message #351457 is a reply to message #351456] Tue, 30 September 2008 23:31 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

Hi,
Actually my comid is varchar datatype, I am picking the last character of the data which is numeric and then auto incrementing using max,substring functions.Now how should I do this according to my requirement?

thanks



Very bad method.

Now tell me what happens if 2 users try to insert the data
at the same time.Now both will be having the same numeric value
because of max.

Sequence will always generate a different value in multiuser
enviroment.

What i think that you are having a bad design.And using
cycle in sequence will not save your neck.

Rethink about your design.Why you need the same value again.


Regards,
Rajat


Re: reset auto generate from 1 when academic year changes [message #351459 is a reply to message #351456] Tue, 30 September 2008 23:37 Go to previous messageGo to next message
rajuakula
Messages: 63
Registered: March 2005
Member
as my academic year starts from ( dec 1s and ends at nov 30th )it should restart from 1, that is what I am confused, how to do that?

thanks
Re: reset auto generate from 1 when academic year changes [message #351462 is a reply to message #351459] Tue, 30 September 2008 23:47 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Have you tried what i have posted.

Create that sequence and run that select statement again and again.

it will generate values like this

First Cycle:-
RAJAT1,RAJAT2,RAJAT3,RAJAT4

Second Cycle:-
RAJAT1,RAJAT2,RAJAT3,RAJAT4

And so on.


Regards,
Rajat
Re: reset auto generate from 1 when academic year changes [message #351463 is a reply to message #351462] Wed, 01 October 2008 00:01 Go to previous messageGo to next message
rajuakula
Messages: 63
Registered: March 2005
Member
yes, I tried your code.It is working but how do I restrict between the academic year.once academic year changes, it should start from 1 again.According the reply you sent, after 4, it is repeating again.In my case,I dont know how many records will be inserted by user between Dec 1st 2008 to nov 30 2009, might be 100 or 200 or more.once academice year completes then it should start again from 1.How do I need to implement this?
very urgent.

thanks
Re: reset auto generate from 1 when academic year changes [message #351465 is a reply to message #351463] Wed, 01 October 2008 00:13 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You have to check everytime before generating value that
it's a new academic year or not.You can keep that value in a
table and check it with sysdate.

If it's new academic year you can reset the sequence value
by analysing it's current value and using alter command
to reset it's value.

./fa/456/0/ google on sequences.

Check This

But i will again say rethink your design.It's not a scalable
solution.


Regards,
Rajat
Previous Topic: Select Date/Time past 00:00:00?
Next Topic: Query Optimization
Goto Forum:
  


Current Time: Thu Dec 08 20:10:36 CST 2016

Total time taken to generate the page: 0.11360 seconds