Hey Alastair, We have kinda been in need of something like this for a while too...we have a tranmaster table that stores transaction ids and right now we do a select max(tranid)+1 from tranmaster to get the next tranid but as transactions are deleted and such, it leaves alot of holes in the id sequence.
It has about 10,000 rows in the table, but the max id is up near 45,000! One way we thought about aproaching this problem was to make a utility that would renumber all the tranids in tranmaster as well as all the other tables that use the tranid. That would make it so our 10,000th row would also be tranid 10,000 instead of 45,000 so wed be back on track. Im not sure how you get your ids or what they represent but if you do it like us and just get the max(id)+1 to get the next, this might be of use...im still keepin my eye out for a better solution too though so im glad you brought this topic up! ----- Original Message ----- From: "Alastair Burr" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Monday, September 08, 2003 3:02 PM Subject: [RBASE-L] - Finding unused id codes > Hello everyone, > > I'm trying to find what codes from a look-up table are not currently in use: > > I have a data table that has, amongst other columns, a text column with a > (variable) number of 2 character codes separated by a comma, viz: > aa,ab,ac,ba,bb,bc... > > The look-up table has code column with a row for each code and a column with > a description. > > I can find which codes are in use easily enough and I thought that the > missing ones could be found by "reversing" the logic but it doesn't work: > > Briefly, SELECT... WHERE ... CONTAINS tell me the ones in use but SELECT... > WHERE ... NOT CONTAINS gives me everything. > > I can think of a way to do it using a temp table but I wondered if there was > an SQL way to find the unused items? Anybody got any ideas? > > Thanks in advance, > Regards, > Alastair. > > ---------------------------------- > A D B Burr, > St. Albans, UK. > ---------------------------------- > [EMAIL PROTECTED] > ---------------------------------- >
