|
yeah...upper limits is what we're worried
about, not yet but some day!
With some clients we have to do periodic
imports of 20k rows of data where we delete the old rows and add in the new rows
so it can chew up the ids pretty quick.
Just formin a game plan for when/if we ever start
to get low :P
i misunderstood the origional question though, it
looks like he had another table that gave valid id's and he wanted to find which
ones werent used.
----- Original Message -----
Sent: Monday, September 08, 2003 3:28
PM
Subject: [RBASE-L] - Re: Finding unused
id codes
I don't se any problem with the holes. Is it just
aesthetics? The program doesn't care if the
data has holes, does
it? I use autonum columns in every table and have long since
forgotten
about the holes caused by deletions, abandonments,
etc. Swiss cheese tastes good to me.
I suppose one would have to
worry about reaching the upper limits of integer generations;
I should be
so lucky as to have that many zillions of
transactions...
bill
Atrix Wolfe wrote:
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]
----------------------------------
.
|
