RE: Missing numbers ( was Re: )

David,

provided that your reference number is the first key field in a logical
over the file, I'd definitely go the RPG + SETLL route.

That way you can use a simple FOR-loop to get the first and/or all
available numbers.

While SQL may be quicker (on our system, finding the missing number
1.473.475 took 5 seconds in SQL versus 13 seconds in RPG), I find an RPG
solution more intuitive.

SQL seems kludgy (sp?) to me in this context, especially since I assume
that you're doing this in a program anyway.

Just my opinion, of course.


Peter Colpaert
Software Engineer, Consumer Luminaires

Industrieterrein Satenrozen 11, 2550 Kontich, Belgium
Tel. +32/ 3 450 74 09, Fax +32/ 3 450 74 33, Internal 1317
peter.colpaert@xxxxxxxxxxx, www.philips.com



The information contained in this message is confidential and may be
legally privileged. The message is intended solely for the addressee(s).
If you are not the intended recipient, you are hereby notified that any
use, dissemination, or reproduction is strictly prohibited and may be
unlawful. If you are not the intended recipient, please contact the sender
by return e-mail and destroy all copies of the original message.






David FOXWELL <David.FOXWELL@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
17/04/2008 09:47
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
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?