Re: SQL - find 'missing' numbers

[Charles Wilt <charles.wilt@xxxxxxxxx>]

It's actually pretty easy to do...

Chuck mentions a RCTE, but the better solution IMHO is to set up a
"numbers" or "tally" table.

This is just a table that contains numbers from 1 to 1 million or
whatever...

So your statement would be simply
select NBR
FROM NBR_TBL
  where NBR not in (select "number" from file)

You could also do it as a join
select NBR
from NBR_TBL
        left exception join FILE on NBR = "number"

Prior to RCTE, it was the only way to handle this request and there is
(probably) still a performance benefit to having the numbers table
pre-generated instead of generating it on the fly for every request.

Google SQL NUMBERS TABLE and do some reading...
https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

HTH,
Charles

On Tue, Sep 29, 2015 at 11:45 AM, T. Adair <tadair@xxxxxxxxxxxxxxxx> 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...
>
> select "value"
>   where "value" not in (select "number" from file)
>
> It's more complex than that, but if I can get the basics then I should be
> able to expand it.
>
> 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.
>
> I've tried using CREATE SEQUENCE and it creates a Data Area (with junk in
> it).
>
> 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.
>
> 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.
>
> Thanks in advance,
>
> Tim
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.