Re: Missing numbers ( was Re: )

Tom,

I haven't determined whether [+1] is sufficient. In

the example, both '7' and '8' are sequentially missing. Would [+1]
find '7' but miss '8'?

The original request was ambiguous about that. In one place the poster
mentioned getting a list of the missing numbers, which would include both 7
and 8 in the example. But then he also said "I need to find the smallest
missing number in a given range of numbers." For example, if the goal is to
obtain the lowest available number to use as a key you'd only need to know
the smallest missing number.

Maybe more interesting but also without doing any testing, I wonder

if lookahead fields in RPG might be useful.

I've never quite understood why the restriction on using program described
files exists for look ahead fields. It wasn't always used just to
predetermine what the next record type would be in a card deck.

However, in this case I'm not sure why a lookahead would be any better than
just reading the file and detecting when the record just read is not the
next consecutive integer after the previous read. In either case you can
tell how many sequential numbers are missing for each gap found. And if the
goal is to just find the smallest missing number, then you can terminate
after the first gap is detected.

I'm with Joe here -- if there is a pre-existing index on the field that
needs to be tested, a HLL program loop seems like a better tool for the job
than SQL. Given a number range, you just SETLL to the start of the range.
If it is not in use, you already have the smallest missing number. Then you
just read the file by key, and the the first (or each) time the value is not
one higher than the previous read, you found another gap. Then you either
quit if you only needed the smallest number, or continue until you have
reached EOF or the high end of the number range requested.

As also mentioned, having a logical with only the single field may help
minimize the overhead of reading via HLL instead of the SLIC layer of the
SQL engine. But a HLL has to do so much less work than the suggested SQL
statements, that it is hard to imagine the SQL being more efficient unless
the number range is relatively large.