OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Gaps in Data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Gaps in Data [message #38785]

Tue, 14 May 2002 21:44

selvi
Messages: 2
Registered: May 2002

Junior Member

Hi. I need to write a select statement which will tell me whether the data in a table is in sequence.
e.g.,
TableA
1
2
4
5
6
The select statement should tell me that the data in TableA is not in sequence. Thanks.

Report message to a moderator

Re: Gaps in Data [message #38786 is a reply to message #38785]

Wed, 15 May 2002 03:19

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I've a table MHE_TEST(col_1 number) containing the following:
SQL>select * from mhe_test;

COL_1
-----------
1
2
4
5

I've created an anonymous PL/SQL block:
=================================
SQL>ed
Wrote file afiedt.buf

1 declare
2 cursor c_max
3 is
4 select max(col_1) maximum
5 , min(col_1) minimum
6 from mhe_test;
7 cursor c_temp(cv_col_1 number)
8 is
9 select col_1
10 from mhe_test
11 where col_1 = cv_col_1;
12 v_max number;
13 v_min number;
14 v_temp number := NULL;
15 i number;
16 err_rec number;
17 begin
18 open c_max;
19 fetch c_max into v_max,v_min;
20 close c_max;
21 for i in v_min..v_max loop
22 open c_temp(i);
23 fetch c_temp into v_temp;
24 if c_temp%NOTFOUND THEN
25 err_rec := i;
26 v_temp := NULL;
27 close c_temp;
28 exit;
29 else
30 close c_temp;
31 end if;
32 end loop;
33 IF v_temp IS NULL then
34 dbms_output.put_line('Out of sync on number: '||err_rec);
35 ELSE
36 dbms_output.put_line('In sync.');
37 END IF;
38* END;
SQL>/
Out of sync on number: 3

PL/SQL procedure successfully completed.

SQL>

========================

Note, this is from the top of my head, so it is just a pointer for the direction to look, not the *best* solution around.

MHE

Report message to a moderator

Re: Gaps in Data [message #38801 is a reply to message #38785]

Wed, 15 May 2002 23:35

Maaher
Messages: 7065
Registered: December 2001

Senior Member

thanks Rick! It is a simple, yet beautiful solution. It is nice to see that there are still some people awake! I can't believe I looked over this simple solution!

If you issue the following Select, you'll get the numbers that are missing in the sequence:
===================
SQL> select * from mhe_test
2 /

COL_1
----------
1
2
4
5
7

SQL> SELECT (col_1+1) miss
2 FROM mhe_test a
3 WHERE NOT EXISTS (SELECT 1
4 FROM mhe_test b
5 WHERE b.col_1 = a.col_1+1
6 )
7 AND a.col_1 != (SELECT MAX(col_1)
8 FROM mhe_test
9 )
10 /

MISS
----------
3
6

SQL>
===================

HTH,
MHE

Report message to a moderator

Previous Topic:	v7.3.4 execute immediate
Next Topic:	Copy Command Error Message

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 22:33:04 CDT 2024