Sequence in SQL*FORM (help required)

Hello everybody

I have a question regarding the SQL Form. I would like to hear a suggestion from you. Any response is greatly apprepriated.

I have created a form. One of the fields, called REF_NO, is defined as unique, not null number. It takes a number from a definied sequence which start with 2000. My objective is to have a number in the field every time I want to add a new entry. It should always start from the smallest available value. I defined the FIELD DEFAULT for REF_NO as :sequence.sirseq.nextval, where 'sirseq' is the sequence name. When I excuted the form, it started from 2000 as I expected. The problem I have is that every time I excute the form, the number increased by 1, even there is no new entry. My question is how to generate the REF_NO which always take the number one after the last entry.

My another question is this. I have some entries in the form inserted by 'insert into' in SQL, which took the REF_NO from 2000 to 2010. The REF_NO in the form takes the number from the same sequence described above. When I excuted the form I expected the number REF_NO start from 2011. But it always started from 2000. I would like to know how to solve this problem.

Thank you for your attention.

Wei Received on Tue Nov 08 1994 - 11:07:58 CET