Re: Finding missing integers

Joseph Arcieri (arcieri@pilot.njin.net) wrote: : Hi, I'm not sure how to do this. I'm running SQL v4/SE v5 and have several : databases that use an integer field as the record id. Is there a way to : find skipped or unused integers in the field? e.g. Each row that's added : has a unique number added automatically, but when rows are deleted, those : numbers are available to be used again. How can I search for used numbers/integers in a range? Make senes?possible? Thanks, Joe Arcieri : First advice is to not worry about them, but if you insist on finding them, I'll show you the 4GL way, and the same logic with slightly altered syntax would work in a stored procedure: DECLARE c_count CURSOR FOR SELECT pk FROM table ORDER BY pk LET testpk = 0 FOREACH c_count INTO lpk LET testpk = testpk + 1 WHILE testpk != lpk # testpk was not in the database table # do wahtever you want with this information, like: INSERT INTO missing_pk_table VALUES (testpk) LET testpk = testpk + 1 END WHILE END FOREACH ======================================================================= Dennis J. Pimple dennisp@informix.com Opinions expressed Principal Consultant -------------------- are mine, and do not Informix Software Inc Voice: 303-850-0210 necessarily reflect Denver Colorado USA Fax: 303-779-4025 those of my employer.