pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-novice@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Fun with SQL
Date: Wed, 06 Mar 2002 12:19:03 -0800
Message-ID: <web-819041@davinci.ethosmedia.com> (raw)

Folks,

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
 usage.

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.

THE QUERY:
SELECT (to_number(invoice_no, '99999')::INT4 + 1::INT4) as skipped
FROM invoices
WHERE ltrim(to_char((to_number(invoice_no, '99999')::INT4 + 1::INT4),
 '99999'))
  NOT IN (SELECT invoice_no FROM invoices);

WHAT I GOT:
skipped
-------
  10519
  10839

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."

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus



Message-ID: <web-819041@davinci.ethosmedia.com>
Permalink:  ../web-819041@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-819041@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, pgsql-novice@postgresql.org
  Subject: Re: Fun with SQL
  In-Reply-To: <web-819041@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox