RE: Missing numbers ( was Re: )

I'd go with RPG, but not with a SETLL since you're randomly checking to see if the entry exists.

Granted, you'll save some by not bringing any data back to the program.

Instead, as I suggested before:
Build an SQL index on NUMBER
Create a DDS logical containing just NUMBER, and keyed on NUMBER.

Now just do SETLL to position, and READ in a loop till you find a missing number.

I think the benefits of sequential access vs. random will trump the benefits of existence checking vs.
reading.

Heck try them both out, but in either case, make sure you are using an SQL created index instead of a
DDS defined logical. If you're going to use SETLL, you don't need the DDS logical above. For read,
you'd want it to minimize the data that gets brought in. If reading, be sure to specify BLOCK(*YES)
on the f-spec.


HTH,

Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Peter.Colpaert@xxxxxxxxxxx
Sent: Thursday, April 17, 2008 4:30 AM
To: Midrange Systems Technical Discussion
Subject: 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?
--

--
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.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.