Create Sequence Number Using Current Year

[Spenello]

We've been using sequence numbers in our Oracle 10g database with the folowing code:
--CREATE THE SEQUENCE FOR THE PRIMARY KEY
CREATE SEQUENCE [SCHEMA].[PK]_SEQ
START WITH 1
INCREMENT BY 1
MINVALUE 1
NOCACHE
NOCYCLE
ORDER;

The challenge is to create a sequence using the current year in the following format: FY9999
Where FY = "fiscal year", in this case it would be 08. Next year it would be 09. Then the next four digits would be the sequence starting with 0001. So, the sequence would look something like this.
080001
080002
080003
Anyone do anything like this before?

 

[taupirho]

Try this for size

select to_char(sysdate,'YY') || to_char(my_seq.nextval,'FM0009')
from dual



In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Spenello,

As a warning, your Primary Key composition disobeys at least one of the Seven Rules for Well-Behaved Primary Keys:

Primary Keys Should be Stupid: Primary Keys should not represent any meaningful, business intelligence. By including the fiscal year in your primary keys, you are embedding business intelligence where it should not reside. The fiscal year, if it is important to store in your data, should be a piece of business information separate from the Primary Key.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Spenello]

Thanks, taupirho. Changed 'FM0009' to '0000'.

We're bound by business rules that sometimes are made by people who don't understand technical processes. We'll make a new primary key and use the FY9999 sequence in a separate column.