Re: Oracle Serial numbers

In <32537B58.5C35F264_at_pacbell.net> hue le <huele_at_pacbell.net> writes:
>
>huele_at_pacbell.net wrote:
>>
>> I want to have serial number on my table record by using "nextval",
 but
>> the number go from 1,2,3,4,5 then 21,21,24,25. Any expert can help
 me
>> out?
>> Thanks alot.

Having only two samples rather than the required three, here is a reasonable attempt at the solution.

Asumption: the numbers are sequential 1 to 5 in the one's column and jump by 20 after each sequence.

Solution:

column serial_number newvalue max_sn

select

    max(serial_number) serial_number
from the_table;

insert into the_table (the_sn_col)

    values

        (
        decode
            (
            mod(max_sn,5)
            ,0, max_sn + 21
            ,max_sn + 1
            )
        )

This can be done as a trigger if needed. The sequence number doesn't work in this scenario since you have a disjoint series rather than a sequence. Received on Sun Oct 06 1996 - 00:00:00 CEST