I like tidyness too, i didnt know that some people say that "it shouldnt matter" but i dont doubt it cause i can see where they are coming from.
I used to get in alot of heated debates where i would try to do crazy things just to make optimum code , where the other people often said "premature optomization is the root of all evil" so i guess its the same thing like "dont worry about it, your taking away from other things that matter more" i guess. I think the way im gonna tackle my problem is project tranids from tranmaster and then add another integer column and autonum that column and use that table as a translation from old tranids to new tranids. anyways, your problem looks difficult, especialy difficult to find an elegant solution. Have you tried something like this? Its a little brute forceish but ive come to accept that the real world doesnt always need theoreticly pure, 100% awesome solutions sometimes (grudgingly!) :P check out the attached command file, put it in its own directory and run it and it will make some database files, populate the tables of idlists and ids and then will give you a list of the unused ids. ----- Original Message ----- From: "Alastair Burr" < > To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Monday, September 08, 2003 11:08 PM Subject: [RBASE-L] - Re: Finding unused id codes > Thanks Atrix, > > I think I would tackle your problem by using a while loop which starts at > the min(idnum) and goes up to the current max(idnum) and creates a (temp) > table where it doesn't find a match. Even with a large data set this would > be very fast with a temp table. > > If you really want to re-use the currently unused numbers then instead of > using an autonum column you would need to take the idnums from the temp > table first then add/redefine the autonum definitions when the unused > numbers are all used. It's not as hard as it might sound. > > If it's really a problem that you never have an unused number then create a > control table that contains all the possible numbers and a flag to indicate > whether it's used or not and take your next number(s) from the control > table. Alternatively, put the unused numbers and the flag in the data table > if you know that you have very few holes and mostly add new numbers at the > end - then use the flagged number before adding new ones. > > This subject always causes quite heated debate on this list with half saying > not only that it doesn't matter but also that it mustn't matter and the > other half liking things tidy for some good reason. Me? I'm a tidy person - > where possible. > > Regards, > Alastair. > > > ----- Original Message ----- > From: "Atrix Wolfe" <[EMAIL PROTECTED]> > To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> > Sent: Monday, September 08, 2003 11:11 PM > Subject: [RBASE-L] - Re: Finding unused id codes > > > > 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] > > > ---------------------------------- > > > > > >
problem.cmd
Description: Binary data
