RE: Missing numbers ( was Re: )

Thanks for all the replies and for giving my post a subject name.

Feeling a bit embarassed seeing how much of people's time I took up and how much time I spent on Midrange.com yesterday and still not getting an easy solution to my problem - sorry, don't wish to sound ungrateful.

My op doesn't seem to have been to clear. In fact the problem concerns our archive management system. Each document archived has its own reference number in order to find it on a shelf in the archives. For example the shelf TX can contain documents in positions TX00001 to TX99999.

The file used for stocking these numbers only contains those positions that are actually occupied. For example, I may only have TW00001, TW00002, TW00004, TW00005 in the file.

When a document is archived I need to give it the first available position for the shelf, so in the above example it would be TW00003. At the moment the program loops from 1 to 99999 and does SETLL (yes RPG not SQL ) to see if the number exists or not and so finds the first available.



To find the missing numbers in a series, I came up with this awful idea :

1. Create a file containing all the numbers, say 1 to 100.

create table qtemp/oneToAHundred as ( select anyfield as number from
Afilewithatleast100rows fetch first 100 rows only ) with data

update qtemp/oneToAHundred
set number = rrn(onetoahundred)

2. List the missing numbers.

SELECT * FROM onetoahundred exception join myfilewithTheMissingNumbers on number
= myfilewithTheMissingNumbers.number

Now that I look at it again, it doesn't seem that bad.
Does it?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.