Re: Serial Number within a group
>From: uunet!cdin-1.compu.com!mark (Mark Heringslake)
>Date: Mon, 1 Mar 93 9:13:25 EST
>Subject: Re: Serial Number within a group
>X-Informix-List-Id: <list.1951>
>Quoting Tang Chang Thai.....
>< hi ... has anyone ever implemented a scheme to generate serial numbers that
>< stays within a group? I know that Informix supports serial number that is
>< table wide. However I require something like the following:
>< Table A
>< -------
>< Col1 Col2
>< --- ----
>< 1 1
>< 1 2
>< 1 3
>< 1 4
>< 2 1
>< 2 2
>< 3 1
>< 3 2
>< etc.
>< Col1 is the group while the numbers in Col2 is serial within its group.
>< Would be grateful for any contributions.
> Try this.....
> LET WCol1 = Col1
> LET WCol2 = NULL
> SELECT MAX(Col2) INTO WCol2 FROM <table> WHERE Col1 = WCol1
> IF WCol2 IS NULL THEN
> LET WCol2 = 0
> END IF
> LET WCol2 = WCol2 + 1
> Now the only thing you need to be careful of is exceeding the maximum
> value for Col2 (e.g. trying to place a value > 32,767 into a SMALLINT).
There are a couple of problems with this solution,. First, the performance
of the MAX function may not be as fast as you would want. Second, two
poeple can do it at the same time, arrive at the same answer, but only one
of them would be able to insert their row of data.
A more general solution uses a second table:
CREATE TABLE keyvals
(
col1 INTEGER NOT NULL PRIMARY KEY CONSTRAINT pk_keyvals,
col2 SMALLINT NOT NULL CHECK (col2 >= 0) CONSTRAINT c1_keyvals
);
Inside your transaction, you use:
DECLARE c_whatever CURSOR FOR
SELECT col2
INTO col2_value
FROM keyvals
WHERE col1 = col1_value
FOR UPDATE
OPEN c_whatever
FETCH c_whatever
IF (STATUS = NOTFOUND) THEN
LET col2_value = 1
INSERT INTO keyvals (col1, col2) VALUES (col1_value, col2_value)ELSE
LET col2_value = col2_value + 1
UPDATE keyvals SET col2 = col2_value WHERE CURRENT OF c_whatever
END IF
This has the advantage that if you have to rollback the transaction, no
change has been made to your keyval table, and no-one else can be messed up
because if they are playing with some other col1 value, then there is no
conflict, and if they are playing with the same col1 value, then until the
first transaction has decided whether it is committed, they cannot allocate
a number from the keyvals table.
Of course, if the col2 values do not have to be contiguous, merely
increasing, then a table with only a serial column in it is quite adequate
instead of the keyvals table shown above; you simply get a new serial
number from that table by inserting a row and deleting all rows from the
table -- because it should normally be empty.
Yours,
Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>