RE: Missing numbers ( was Re: )

[Peter.Colpaert@xxxxxxxxxxx]

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?