[GENERAL] Sequences

Hi,

 

I need to do something with a sequence. This is the example:

 

CREATE TABLE TABLE1(

 

    PRIMARY1 INTEGER NOT NULL,

    OTHER1 VARCHAR (20) NOT NULL,

    CONSTRAINT "MYPK1" PRIMARY KEY (PRIMARY1)

 

);

 

CREATE SEQUENCE "SEQ_1" INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 CACHE 1 NO CYCLE OWNED BY TABLE1.PRIMARY1;

 

CREATE TABLE TABLE2(

 

    PRIMARY1 INTEGER NOT NULL,

    REF1 INTEGER NOT NULL,

    OTHER1 INTEGER NOT NULL,

    OTHER2 VARCHAR(20),

    CONSTRAINT "MYPK2" PRIMARY KEY (PRIMARY1),

    CONSTRAINT "MYREF1" FOREIGN KEY (REF1) REFERENCES TABLE1(PRIMARY1),

    CONSTRAINT "MYUK1" UNIQUE (REF1,OTHER1)

 

);

 

CREATE SEQUENCE "SEQ_1" INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 CACHE 1 NO CYCLE OWNED BY TABLE2.PRIMARY1;

 

 

 

TABLE1

 

PRIMARY1        OTHER1

1                        A

2                        B

3                        C

 

 

With this two tables I wan to create a sequence that do:

 

 

TABLE2

 

PRIMARY1        REF            OTHER1            OTHER2

1                        1                1                       A

2                        1                2                       B

3                        1                3                       C

4                        2                1                       A

5                        2                2                       B

6                        3                1                       A

7                        3                2                       B

8                        3                3                       C

9                        3                4                       D

10                      1                4                       D

11                      1                5                       E

12                      3                5                       E

13                      2                3                       E

 

 

I want to create a sequence that increases in unit column 3 for each record individually in column 2

 

How do i create a sequence that can manage this?

Is there a solution for this?

 

 

Thanks

 

Oscar Uribe