Re: please help = pl/sql

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Wed, 25 Jun 2003 13:43:33 +0400
Message-ID: <bdbqsd$d5q$1@babylon.agtel.net>

"JDC" <tradeNOSPAM_at_localnet.com> wrote in message news:vfi2e8h8dv8ba3_at_corp.supernews.com...
> Hello all,
> I hope this newsgroup is the correct one for my question....
>
> essentially, I am trying to use PL/SQL to:
> search a table, and find records that are missing from the sequential
> order...
> *there could be double records, but there cannot be gaps**
> we need the value of the gaps printed to the screen
> for example, the records could be
> 14002
> 14003
> 14003
> 14003
> 14004
> 14004
> >> (this should be anything from 14004 - 14009)
> 14010
> 14011
> 14012
> -----------------------------------------------
> so the output should be something like:
> 14002
> 14003
> 14003
> 14003
> 14004
> 14004
> >> *A gap was found: length of gap = 5
> 14010
> 14011
> 14012

Maybe something like this would do:

DECLARE
     l_lastval invoice_detail.invoice_no%TYPE; BEGIN
   FOR rec IN (SELECT DISTINCT invoice_no, invoice_date

                 FROM invoice_detail
                WHERE invoice_no > :range_start AND invoice_no < :range_end
                ORDER BY 1) 
   LOOP
     IF (l_lastval IS NOT NULL) AND (rec.invoice_no - l_lastval) > 1 THEN
       -- gap found
       dbms_output.put_line('>> * Gap found, length = '||to_char(rec.invoice_no - l_lastval - 1));
     END IF;
     dbms_output.put_line(to_char(rec.invoice_no)||' - '||to_char(rec.invoice_date,'MM/DD/YYYY'));
     l_lastval := rec.invoice_no;

   END LOOP;
END; Remove DISTINCT if you want duplicates to be printed out.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

Received on Wed Jun 25 2003 - 04:43:33 CDT