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


This thread ...

Follow-Ups:
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].