How Do I Find What's Not There?

Published: 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

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot