RE: Missing numbers ( was Re: )

["Wilt, Charles" <WiltC@xxxxxxxxxx>]

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.