SQL - find numbers missing from a specified range of numbers

Eric Lehti

VIP Member
SQL at V5R4 has this capability. I want to produce list of order numbers in the range 346290 and 346298 that are not in a file F4006. I have these records in F4006.
....+....1....+....2....+....3....+....4....+....5
Order Or Order A Mailing
Number Ty Co T Name
-------- -- ----- - --------------------
346292 SC 00003 2 Allied Fence
346304 SL 00003 2 B & B Fence Construction
******** End of data ********





with list (Number) as
(select 346290
from qsys2/qsqptabl
union all
select Number+1
from list
where Number < 346299)
select Number from list
where Number not in
(select oadoco
from f4006
order by 1

....+....1....
NUMBER
346,290
346,291
346,293
346,294
346,295
346,296
346,297
346,298
346,299
******** End of

http://www.itjungle.com/fhg/fhg101409-story02.html
 
Back
Top