Re: SQL - find 'missing' numbers

On 29-Sep-2015 09:45 -0600, T. Adair wrote:

I am trying to find a way in SQL to get a list of all values that do
not exist in a field. As in...

<<SNIP>>

Let's say I have a file that has a field "number". It's a 3-digit
field. I know that over 800 of the 999 possible (non-zero) values are
already used. I need to know which values are not used.

<<SNIP>>

I suppose I could create a file of nothing but sequential numbers
from 1 to whatever, but I'm hard-headed and this really shouldn't be
that hard.

A Recursive Common Table Expression (RCTE) [or Recursive CTE] can easily generate a temporary result with those numbers, scoped to the run of the query, if there is no [intention to maintain an] existing file for that purpose.

Am I missing something obvious? I can probably get around this
particular issue, but I'd really like to have a new technique that I
can use in the future.

I can not test, but the following use of EXCEPT I think should suffice:

with
in_seq (seq_nbr) as
( values(1)
union all
select seq_nbr+1
from in_seq
where seq_nbr<999
)
(select seq_nbr
from in_seq
except
select the_numeric_col
from the_file
) order by 1