Hello everyone:

I try to create a sequence with following requirements:

create a sequence that will generate integers starting with the value 9. Each value should be three less than the previous value generated. The lowest possible balue the sequence should be allowed to generate is -1, and it should not be allowed to cycle.

I think values generated by this sequence are 9, 6, 3, 0

my sql statement goes like this:

SQL> create sequence MY_FIRST_SEQUENCE
2 increment by -3
3 start with 9
4 minvalue -1
5 nocycle;
create sequence MY_FIRST_SEQUENCE
*
ERROR at line 1:
ORA-04004: MINVALUE must be less than MAXVALUE

I don't know what's the maxvalue in this squence. Should it be 9 if that's the case. why do I get a error message?

Plase give me some suggestio on how can I make this sequence work. Thanks!

sjgrad03
12-03

Even without knowing anything about sequences, if this is a DESCENDING sequence and it STARTS WITH 9, then it is logical that its MAXIMUM VALUE is 9.

Your statement will be executed properly if you add MAXVALUE n, where n >= START_WITH_value. If it is not clear enough, here's an example:

CREATE SEQUENCE my_seq
START WITH 9
MAXVALUE 9
MINVALUE -1
INCREMENT BY -3;

or

CREATE SEQUENCE my_seq
START WITH 9
MAXVALUE 1000
MINVALUE -1
INCREMENT BY -3;

NOCYCLE is the default and it is not necessary to specify it.

Read more about creating Oracle sequences here.