Home » SQL & PL/SQL » SQL & PL/SQL » To retrieve the numbers which are not presented in the table
To retrieve the numbers which are not presented in the table [message #191136] Tue, 05 September 2006 00:20 Go to next message
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 #191139 is a reply to message #191136] Tue, 05 September 2006 00:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT LEVEL AS no
FROM dual
CONNECT BY LEVEL <= (SELECT max(no) FROM nums)
MINUS
SELECT no
FROM nums


Ross Leishman
Re: To retrieve the numbers which are not presented in the table [message #191316 is a reply to message #191136] Tue, 05 September 2006 12:01 Go to previous messageGo to next message
ian55
Messages: 1
Registered: September 2006
Location: UK
Junior Member
Or an outer join

SELECT distinct days.no
FROM
(
SELECT LEVEL AS no
FROM dual
CONNECT BY LEVEL <= 5
) days
, nums
WHERE days.no = nums.no (+)
AND nums.no is null


You could even use a pipelined function to generate the all the days of your month. See

http://asktom.oracle.com/pls/ask/f?p=4950:8:10405674977420507883::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:12841974988629
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 Go to previous messageGo to next message
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
icon14.gif  Re: To retrieve the numbers which are not presented in the table [message #193401 is a reply to message #191136] Sat, 16 September 2006 04:44 Go to previous message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Hi,
create a table T1 having a column 'NUM'which contains number from 1 to 20.
select NUM from t1
minus
Select No. from Table;

It will display the numbers that are not present in your table.

Regards
Jaydeep
Previous Topic: Update statement
Next Topic: Parallel Hint - nodes usage
Goto Forum:
  


Current Time: Wed Dec 07 18:58:34 CST 2016

Total time taken to generate the page: 0.09393 seconds