How Do I Find What’s Not There?
October 14, 2009 Hey, Ted
|
Our inventory items are assigned seven-digit numbers. When we accept an order for a custom product, we assign it a number, build the product, ship it, and retire the number. After a few years, the system automatically purges these numbers, which means we are free to reuse these numbers. Is there a way to make an SQL query find the numbers within a range that are not assigned to items? –Tim I’ll give you a method that works at V5R4 or above, Tim. Let’s say your database file is IBM‘s famous QIWS/QCUSTCDT, and that you want to find available customer numbers in the range 938470 through 938479. There is only one customer in that range–G. K. Henning, who has been assigned the number 938472–so we should get a list of nine available numbers. Here’s the query. with list (Number) as
(select 938470
from qsys2/qsqptabl
union all
select Number+1
from list
where Number < 938479)
select Number from list
where Number not in
(select cusnum
from qiws/qcustcdt)
order by 1
Now let’s tear this apart. The recursive common table expression, list, builds a temporary table of all the whole numbers in the range 938470 through 938479. Following the common table expression, the main select retrieves all numbers in list that are not found as customer numbers in QCUSTCDT. Voilà! –Ted
|
