Jonathan,
You don't say which VRM you're running, but for V5R4, pp. 402-403 in
Graeme Birchall's DB2 SQL Cookbook (version LUW 9.5) matches your
problem. "One often has a sequence of values (e.g. invoice numbers) from
which one needs both found and not-found rows...." Uses recursion
(available in V5R4+) to build a set of values to find, and joins back to
your invoice file to find missing values.
You can find Graeme's (free) ebook at
http://mysite.verizon.net/Graeme_Birchall/id1.html
Click the link for DB2 V9.5 SQL Cookbook (1403K).
Loyd Goodbar
Business Systems
BorgWarner Shared Services
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jonathan Mason
Sent: Thursday, August 07, 2008 9:46 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Finding gaps in a sequence of invoice numbers using SQL
Hi Everyone
One of our customers has an Invoice Header file that currently has
815,000 records and is growing daily and I need to try and identify any
blocks of missing numbers. I could do this quite easily with a simple
RPG (or even CL!) program, but would have to go through the full change
management rules to get it approved, tested, system tested, user tested,
deployed, etc, and there isn't the time.
Is it possible using SQL to generate a list of invoice numbers where the
next invoice number doesn't exist. For example, if invoice numbers 1,
2, 3, 6, 7, 11, 12 and 15 existed the list would show 3, 7, 12 and 15
(although I'm not too worried about the last one).
I'm doubting there is, but figured I would ask.
Thanks
Jonathan
midrange.com
