I just made some fun and interesting use of 'tricky" SQL I thought I
would share, as it gives an example of both clever SQL and function
THE PROBLEM: One of my clients is reporting skipped invoice numbers in
their database. At a glance, I can't find any. While the client uses
numerical invoice numbers, invoice_no is a VARCHAR field to accomodate
other schemes. How can I check for ALL skipped invoice numbers in the
database? Searching for data that is *not* there, without a reference
list to compare, is a challenge.
THE ANSWER: Look for any invoice number that is not succeeded by the
invoice number which is its immediate numerical successor. I will
also have to do some type conversions to do math on a VARCHAR field.
SELECT (to_number(invoice_no, '99999')::INT4 + 1::INT4) as skipped
WHERE ltrim(to_char((to_number(invoice_no, '99999')::INT4 + 1::INT4),
NOT IN (SELECT invoice_no FROM invoices);
WHAT I GOT:
Note that 10839 is the *next* invoice number to be generated, and as
such, not skipped.
WHAT I TOLD THE CLIENT: "Hmmm, I can only see one skipped invoice in
the last 350 you've run. This does not seem to be a chronic problem."
______AGLIO DATABASE SOLUTIONS___________________________
pgsql-novice by date
|Next:||From: Faudzy Sulaiman||Date: 2002-03-07 03:49:41|
|Subject: Postmaster with -i|
|Previous:||From: Josh Berkus||Date: 2002-03-06 20:00:39|
|Subject: Re: pg equivalent to MS SQL exec command|
pgsql-sql by date
|Next:||From: Josh Berkus||Date: 2002-03-06 20:25:30|
|Subject: Re: where not exists|
|Previous:||From: PG Explorer||Date: 2002-03-06 20:13:45|
|Subject: Re: simple problem|