Re: SQL - find 'missing' numbers

On 9/29/2015 11:45 AM, 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...

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.

Some time ago I watched Birgitta Hauser's presentation at COMMON Europe.
It's on Youtube: https://www.youtube.com/watch?v=VeMAmMYCHI0 Around
minute 26, she presents this:

with counter (nbr) as (
values(10)
union all
select nbr + 10 from counter
where nbr < 250)
select * from counter;

It's a recursive CTE. There's also a page at Developerworks with much
of her presentation:
http://www.ibm.com/developerworks/ibmi/library/i-db2connectby/index.html

I found these easier to digest than the DB2 reference page:
http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcomtexp.htm?lang=en