Re: SQL - Question

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.