Home » SQL & PL/SQL » SQL & PL/SQL » Missing numbers in a sequence (Oracle 10g and Windws)
Missing numbers in a sequence [message #443681] Tue, 16 February 2010 23:29 Go to next message
aijaz786
Messages: 91
Registered: February 2010
Member
create table t (
number_from number,
number_to number);


insert into t values(1,3);
insert into t values(5,9);
insert into t values(10,15);
commit;

I need to create a stored function that could find/return missing numbers between number_from and number_to for each record in table t.

For example:

Number_from Number_to missing

1 3
5 9 4 from previous record, this record is supposed to start from 4

Thanks.


Re: Missing numbers in a sequence [message #443682 is a reply to message #443681] Tue, 16 February 2010 23:35 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Always post what you tried and what you get.
This is a FAQ.
Search before posting.
sriram
Re: Missing numbers in a sequence [message #443683 is a reply to message #443682] Tue, 16 February 2010 23:39 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
I did search but I could not find any such solution.
Re: Missing numbers in a sequence [message #443685 is a reply to message #443682] Tue, 16 February 2010 23:46 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Ok..
Answer these too
What it should return
If the second row starts with 7 and ends with 11?
4,5,6 ?

sriram
Re: Missing numbers in a sequence [message #443687 is a reply to message #443681] Tue, 16 February 2010 23:57 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
if first records ends 5 then second is supposed to start from 6. But if the second starts with 10 then 6789 are missing or not being used .

Re: Missing numbers in a sequence [message #443689 is a reply to message #443685] Wed, 17 February 2010 00:11 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL>  with
  2        data as (
  3          select number_from, number_to,
  4                 lag(number_to) over ( order by number_from) prev_number_to
  5          from tt
  6        )
  7      select number_from, number_to, prev_number_to,
  8             decode(number_from-prev_number_to,2,to_char(prev_number_to+1)||' is missing',
                       'Nothing is missing') extra
  9      from data
 10  /

NUMBER_FROM  NUMBER_TO PREV_NUMBER_TO EXTRA
----------- ---------- -------------- ---------------------------------------------------
          1          3                Nothing is missing
          5          9              3 4 is missing
         10         15              9 Nothing is missing

SQL>


I am Just giving hint modify it as per your second reuirement.

sriram Smile

[Updated on: Wed, 17 February 2010 00:57] by Moderator

Report message to a moderator

Re: Missing numbers in a sequence [message #443692 is a reply to message #443681] Wed, 17 February 2010 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    limits as (
  3      select min(number_from) minnum, max(number_to) maxnum from t
  4    ),
  5    numbers as (
  6      select minnum+level-1 num from limits connect by level <= maxnum-minnum+1
  7    )
  8  select num from numbers
  9  minus
 10  select num
 11  from t, numbers
 12  where num between number_from and number_to
 13  order by 1
 14  /
       NUM
----------
         4

Regards
Michel

[Updated on: Wed, 17 February 2010 00:35]

Report message to a moderator

Re: Missing numbers in a sequence [message #443695 is a reply to message #443681] Wed, 17 February 2010 00:52 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Thanks guys for all your help.

I just need advice. In my database, number_from and number_to are defiend as varchar2 type.

I am getting Invalid number error. My mistake that I forgot to mention that number_from and number_to columns are varchar2 type not number

Please advise.

Thanks.
Re: Missing numbers in a sequence [message #443696 is a reply to message #443681] Wed, 17 February 2010 00:53 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

How can we give dynamic value in

Quote:
connect by level <= ? (here value should be maximum of a table)
without using With Clause.

2nd With Limits and With data is not running on Sql.
Re: Missing numbers in a sequence [message #443697 is a reply to message #443695] Wed, 17 February 2010 00:54 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
BUt your test case showing As NUmbers right ?

sriram Smile
Re: Missing numbers in a sequence [message #443698 is a reply to message #443695] Wed, 17 February 2010 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am getting Invalid number error. My mistake that I forgot to mention that number_from and number_to columns are varchar2 type not number

If numbers are not numbers then either fix your numbers or change the requirements.
We can't choose and do it for you.

Regards
Michel

[Updated on: Wed, 17 February 2010 00:58]

Report message to a moderator

Re: Missing numbers in a sequence [message #443699 is a reply to message #443696] Wed, 17 February 2010 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mamalik wrote on Wed, 17 February 2010 07:53
How can we give dynamic value in

Quote:
connect by level <= ? (here value should be maximum of a table)
without using With Clause.

2nd With Limits and With data is not running on Sql.

Please choose a product that was delivered in this century and stop using products that are more than 10 years old.

Regards
Michel

Re: Missing numbers in a sequence [message #443701 is a reply to message #443699] Wed, 17 February 2010 01:00 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Wed, 17 February 2010 12:29

Please choose a product that was delivered in this century and stop using products that are more than 10 years old.

Perfect Smile

sriram
Re: Missing numbers in a sequence [message #443703 is a reply to message #443681] Wed, 17 February 2010 01:03 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle

we are using oracle 10 g. but with clause is not working . I think there is porblem at our end and we don't know how to use it.

i wrote follwoing query to find out missing number.

select b.ide from a,(select rownum ide from dual connect by level<=100) b
where a.ide(+)=b.ide
and a.ide is null


but i want to give maximum value of serial in place of 100 in connect by level clause.


thanks a lot.

Regards.
Asif.

[Updated on: Wed, 17 February 2010 01:04]

Report message to a moderator

Re: Missing numbers in a sequence [message #443705 is a reply to message #443703] Wed, 17 February 2010 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we are using oracle 10 g. but with clause is not working .

Is not working is NOT an Oracle error message.
So we can't help to this point.

Quote:
but i want to give maximum value of serial in place of 100 in connect by level clause.

I don't know what you mean with this.
If number has to be compute then use a subquery.

Regards
Michel
Re: Missing numbers in a sequence [message #443706 is a reply to message #443681] Wed, 17 February 2010 01:14 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi Michael and Sriram,

In fact, number_from and number_to are defined as varchar2 in database. Please advise me how can I make this code work?

Thanks
Re: Missing numbers in a sequence [message #443712 is a reply to message #443706] Wed, 17 February 2010 01:41 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
did you read this Quote:
If numbers are not numbers then either fix your numbers or change the requirements.
We can't choose and do it for you.



Do you have any problem in converting those into numbers?
Use to_number function.
With that you can get the required one right?


sriram
Re: Missing numbers in a sequence [message #443714 is a reply to message #443681] Wed, 17 February 2010 01:43 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi Sriram,

I used to_number to convert number_from and number_to to numbers but still I am getting Invalid number error.

Regards.

Re: Missing numbers in a sequence [message #443715 is a reply to message #443714] Wed, 17 February 2010 01:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> desc ttt
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 NUMBER_FROM                                                    VARCHAR2(10)
 NUMBER_TO                                                      VARCHAR2(10)

SQL> select * from ttt
  2  ;

NUMBER_FRO NUMBER_TO
---------- ----------
1          3
5          9
10         15
16         19
23         24

SQL>  with
  2        limits as (
  3          select min(to_number(number_from)) minnum, max(to_number(number_to)) maxnum from ttt
  4        ),
  5        numbers as (
  6          select minnum+level-1 num from limits connect by level <= maxnum-minnum+1
  7        )
  8   select num from (
  9          select minnum+level-1 num from limits connect by level <= maxnum-minnum+1
 10        )
 11      minus
 12      select num
 13      from ttt, numbers
 14      where num between to_number(number_from) and to_number(number_to)
 15      order by 1
 16   /

       NUM
----------
         4
        20
        21
        22

SQL>


sriram

[Updated on: Wed, 17 February 2010 01:50]

Report message to a moderator

Re: Missing numbers in a sequence [message #443716 is a reply to message #443706] Wed, 17 February 2010 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are they number or not?

Regards
Michel
Re: Missing numbers in a sequence [message #443719 is a reply to message #443681] Wed, 17 February 2010 01:57 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle

we are using oracle 10 g. but with clause is not working . I think there is porblem at our end and we don't know how to use it.

i wrote follwoing query to find out missing number.


Code: [Select all] [Show/ hide]select b.ide from a,(select rownum ide from dual connect by level<=100) b
where a.ide(+)=b.ide
and a.ide is null

but i want to give maximum value of serial in place of 100 in connect by level clause.


thanks a lot.

Regards.
Asif.
Re: Missing numbers in a sequence [message #443721 is a reply to message #443705] Wed, 17 February 2010 01:59 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Wed, 17 February 2010 12:37
Quote:
we are using oracle 10 g. but with clause is not working .

Is not working is NOT an Oracle error message.
So we can't help to this point.

Quote:
but i want to give maximum value of serial in place of 100 in connect by level clause.

I don't know what you mean with this.
If number has to be compute then use a subquery.

Regards
Michel



Quote:
WITH Clause
The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. This article shows how it can be used to reduce repetition and simplify complex SQL statements.


sriram
Re: Missing numbers in a sequence [message #443729 is a reply to message #443681] Wed, 17 February 2010 02:11 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

thank sriram,
please give me link for article of WITH CLAUSE article.

Regards.
Asif.
Re: Missing numbers in a sequence [message #443731 is a reply to message #443729] Wed, 17 February 2010 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference
SELECT page.

Regards
Michel
Re: Missing numbers in a sequence [message #443738 is a reply to message #443729] Wed, 17 February 2010 02:44 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
It has Oracle document link too...

http://www.oracle-base.com/articles/misc/WithClause.php

sriram
Previous Topic: wats wrong in the query
Next Topic: DATA LEVEL SECURITY
Goto Forum:
  


Current Time: Fri Sep 30 10:38:13 CDT 2016

Total time taken to generate the page: 0.08385 seconds