To retrieve the numbers which are not presented in the table [message #191136] |
Tue, 05 September 2006 00:20  |
priyamudankousi
Messages: 1 Registered: July 2006 Location: Bangalore
|
Junior Member |

|
|
Dear friends,
I have one doubt in SQL.The Problem is
i have one table in that a FieldName "No."
I Store the Values as 1,3,4,6,7,9,11...Upto(20)(they are in
sequence)Assume. Ok.
I need to Write a query to Extract the Value that is not present in the table. That is my output should be 2,5,8,10 (That is missing number i want). How to Extract Please help.
Another best Example
In an Attendance Table of an Employee i Store only the Present Days of a Particular Employee. I Need to Extract the Absent Dates For a Given Month.
Thank you.
WIth Thanks & Regards
Kousalya.M
|
|
|
|
|
Re: To retrieve the numbers which are not presented in the table [message #192665 is a reply to message #191316] |
Wed, 13 September 2006 03:45   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or, to avoid an artificial row generator entirely, you can do this to identify the start and end of gaps in the data:
create table temp_gap (col_1 number);
insert into temp_gap (select lvl from (select level lvl from dual connect by level <= 20));
delete temp_gap where col_1 in (2,5,8,10,13,14,15);
select col_1+1 gap_start
,next_col_1-1 gap_stop
from (select col_1
,lead(col_1) over (order by col_1) next_col_1
from temp_Gap)
where next_col_1 != col_1 +1;
GAP_START GAP_STOP
---------- ----------
2 2
5 5
8 8
10 10
13 15
|
|
|
|