On 01-Dec-2015 03:33 -0600, Jan Grove Vejlstrup wrote:

I have a file with one field:

<<SNIP list of values; see DDL and DML just below:>>

create table qtemp/seqfile (seqnbr integer not null)
-- Table SEQFILE created in QTEMP.
insert into qtemp/seqfile values
(1), (2), (4), (5), (6), (8), (9)
-- 7 rows inserted in SEQFILE in QTEMP.


Is it possible with SQL to get the lowest number *not* in the file,
here 3? Or the highest, here 7.


Or both numbers?:

with
seqnbrs ( cntnbr ) as
( Values( 1 )
union all
select cntnbr+1 from seqnbrs
where cntnbr < (select max(seqnbr) from seqfile)
)
select min(nbr) as min_gap, max(nbr) as max_gap
from table(
select cntnbr from seqnbrs
except
select seqnbr from seqfile
) as X (nbr)
-- report from above query:
MIN_GAP MAX_GAP
3 7
******** End of data ********

-- report from above query after INSERT INTO SEQFILE VALUES(11):
MIN_GAP MAX_GAP
3 10
******** End of data ********

Or just a list of all missing values; i.e. all gaps:

with
seqnbrs ( cntnbr ) as
( Values( 1 )
union all
select cntnbr+1 from seqnbrs
where cntnbr < (select max(seqnbr) from seqfile)
)
select cntnbr as gapnbr from seqnbrs
except
select seqnbr from seqfile
-- report from above query, also after the latest INSERT:
GAPNBR
3
7
10
******** End of data ********

Note: the Exception Join syntax vs EXCEPT must be used when other columns are pertinent for the desired output.


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].