Re: please help = pl/sql

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Tue, 24 Jun 2003 20:10:38 -0700
Message-ID: <3EF912AD.8CA6AD17@exxesolutions.com>

JDC wrote:

> 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
>
> What I am trying to do is print to the screen (and later a dummy table)
> the range thats missing from the list...We are trying to number the
> records sequentially, but as you can see, there could be gaps.
> So, from the above records, I would need to find the difference between
> the record 14004 and 14010
>
> here is what I have so far:
> I load the records into the cursor, but am having trouble finding the
> next sequential record to compare to which ever one the loop is at...
>
> thanks in advance!!!!!!
> =======================================================================
> DECLARE
> v_invoice_no invoice_detail.invoice_no%TYPE; -- this is the record
> v_invoice_tmp invoice_detail.invoice_no%TYPE; -- temp storage
> v_compare_value invoice_detail.invoice_no%TYPE;
> v_compare_value2 invoice_detail.invoice_no%TYPE;
> v_invoice_date invoice_detail.invoice_date%TYPE; --date goes with record
> v_original_value invoice_detail.invoice_no%TYPE;
> v_new_value invoice_detail.invoice_no%TYPE;
> v_result invoice_detail.invoice_no%TYPE;
> v_counter int := 0; /* loop counter */
>
> /********************************************************************/
> CURSOR c_invoice IS
> SELECT DISTINCT invoice_no, invoice_date
> FROM invoice_detail
> WHERE invoice_no > 1000609 AND invoice_no < 1000620
> ORDER BY 1;
> /********************************************************************/
> BEGIN
> OPEN c_invoice;
> DBMS_OUTPUT.PUT_LINE('records processed: ' || v_counter);
> DBMS_OUTPUT.PUT_LINE('Invoice# Date');
> DBMS_OUTPUT.PUT_LINE('=======================');
> LOOP
> FETCH c_invoice INTO v_invoice_no, v_invoice_date;
> v_counter := v_counter + 1;
> v_original_value := v_invoice_no;
> DBMS_OUTPUT.PUT_LINE(v_invoice_no || ' - ' || v_invoice_date);
> IF v_invoice_tmp = v_original_value THEN
> DBMS_OUTPUT.PUT_LINE('test okay for: ' || v_invoice_tmp);
> ELSE
> DBMS_OUTPUT.PUT_LINE('missing record: ' || v_invoice_no);
> DBMS_OUTPUT.NEW_LINE;
> v_result := v_original_value - v_new_value;
> DBMS_OUTPUT.PUT_LINE('result: ' || v_result);
> END IF;
> EXIT WHEN c_invoice%NOTFOUND;
> END LOOP;
>
> DBMS_OUTPUT.PUT_LINE('records processed: ' || v_counter);
> CLOSE c_invoice;
> END;(See attached file: johnsScripts06-24-03.txt)

Looks like something I might have written over a decade ago. Try this:

C OR R PROCEDURE xyz IS

minval PLS_INTEGER;
maxval PLS_INTEGER;

BEGIN
   SELECT MIN(coumn_value)
   INTO minval
   FROM table;

   SELECT MAX(column_value)
   INTO maxval
   FROM table;

   FOR i IN minval .. maxval
   LOOP

      SELECT COUNT(*)
      INTO i
      FROM table
      WHERE column_value = i;

      IF i = 0 THEN
         -- missing value
      END IF;

   END LOOP;
END xyz;
/

Forget the DBMS_OUTPUT screen print. It is inefficient and will quickly overwhelm your ability to do anything with it. Just insert into a table.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Tue Jun 24 2003 - 22:10:38 CDT