For Cheryl - included article - we've got to stop meeting like this

(I include the full text of the bounced mail - since it happens regularly w/ you.) } } ----- Transcript of session follows ----- } 550 <cheryl@rrad05.army.mil>... Host unknown } } ----- Unsent message follows ----- } Received: from hpbs2561.boi.hp.com by hp.com with SMTP } (16.8/15.5+IOS 3.13) id AA22443; Wed, 25 Aug 93 09:06:10 -0700 } Return-Path: <jparker@hpbs2561.boi.hp.com> } Message-Id: <9308251606.AA22443@hp.com> } Received: by hpbs2561.boi.hp.com } (16.8/16.2) id AA11919; Wed, 25 Aug 93 10:09:09 -0600 } From: Jack Parker <jparker@hpbs2561.boi.hp.com> } Subject: Re: Serial } To: cheryl@rrad05.army.mil } Date: Wed, 25 Aug 93 10:09:09 MDT } Full-Name: Jack Parker } In-Reply-To: <9308250322.AA08594@rmy.rmy.emory.edu>; from "cheryl@redriverad-emh1.army.mil" at Aug 24, 93 10:08 pm } Mailer: Elm [revision: 70.30] } } } Cheryl, } } I include something Jonathan wrote on this some time ago. } } ----------------------------------------------- } } >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> } } ----------------------------------- } } cheers } j. } } _____________________________________________________________________________ } Jack Parker - Contractor |"Is it weakness of intellect birdie" } Hewlett Packard, BSMC Boise, Idaho, USA|I cried, "or a tough little worm in } jparker@hpbs2561.boi.hp.com |your little inside", with a shake of } (208) 396-5388 (W) (208) 384-1623 (H) |his head he sadly replied: } | "Oh willow, Oh willow, tit willow". } _____________________________________________________________________________ } Any opinions expressed herein are my own and not those of my employers. } _____________________________________________________________________________ } -- _____________________________________________________________________________ Jack Parker - Contractor |"Is it weakness of intellect birdie" Hewlett Packard, BSMC Boise, Idaho, USA|I cried, "or a tough little worm in jparker@hpbs2561.boi.hp.com |your little inside", with a shake of (208) 396-5388 (W) (208) 384-1623 (H) |his head he sadly replied: | "Oh willow, Oh willow, tit willow". _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________