Home » SQL & PL/SQL » SQL & PL/SQL » Find missing sequence number between given range
Find missing sequence number between given range [message #622984] Wed, 03 September 2014 02:23 Go to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Hi,
I am having a table containing customer_code as column.
Value for customer code is between 51000 to 51999,52000 to 52999,53000 to 53999....

Now, I have to find out missing customer code between given range.

e.g.
1) if user enter 51 then:
find out missing customer code between 51000 to 51999

2) if user enter 52 then:
find out missing customer code between 52000 to 52999


Please suggest me.
Re: Find missing sequence number between given range [message #622985 is a reply to message #622984] Wed, 03 September 2014 02:31 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Pick one of row generator techniques in order to create the whole record set (in your example, 51000 to 51999). Then use MINUS set operator to find the missing values, such as
select value
from row_generator
MINUS
select customer_code
from your_table
Re: Find missing sequence number between given range [message #622986 is a reply to message #622985] Wed, 03 September 2014 02:35 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Error as "Table or View does not exist".
I think it for "row_generator" table.
Re: Find missing sequence number between given range [message #622988 is a reply to message #622986] Wed, 03 September 2014 02:39 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Certainly, I agree with you.

Please, read my whole message, don't blindly copy/paste code I posted. You FIRST have to create a "master" record set that contains ALL records involved. In order to do that, read information behind the link. Just for example:
SQL> select 51000 + level
  2  from dual
  3  connect by level <= 5;

51000+LEVEL
-----------
      51001
      51002
      51003
      51004
      51005

SQL>

This represents the "row_generator" "table" I previously posted. Once you have it, you can include MINUS set operator and your own table.

Re: Find missing sequence number between given range [message #622989 is a reply to message #622984] Wed, 03 September 2014 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also I advise you to use the "Search" command in menu bar.
If you have used it with "Find missing number" you'd get some solutions.

[Updated on: Wed, 03 September 2014 02:46]

Report message to a moderator

Re: Find missing sequence number between given range [message #622990 is a reply to message #622984] Wed, 03 September 2014 02:47 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Or rather than a compound query, you could use an antijoin between the row generator and you table:
select value from row_generator 
where value not in select customer_code from your_table
You would need to handle NULLs appropriately.
Re: Find missing sequence number between given range [message #622992 is a reply to message #622990] Wed, 03 September 2014 03:22 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Thank you very much...
Re: Find missing sequence number between given range [message #622993 is a reply to message #622992] Wed, 03 September 2014 03:46 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
Not working properly..
It is giving same output as normal query.


I am using query as given below:
"
SELECT CUST_CD, CUST_NAME FROM CUST_MST
WHERE CUST_CD LIKE '52%' AND
CUST_CD NOT IN
(
SELECT TO_CHAR(TO_NUMBER(51000 + LEVEL))
FROM DUAL
CONNECT BY LEVEL <=999
)
ORDER BY CUST_CD"

** In the table CUST_MST datatype for CUST_CD is varchar2(6).
Re: Find missing sequence number between given range [message #622994 is a reply to message #622993] Wed, 03 September 2014 03:50 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Please format your code and enclose it within [code] tags, as described here How to use [code] tags and make your code easier to read

Your type casting is wrong. You are using to_number on a number, which will usually give unexpected results.

--edit: you have also reversed the logic.

[Updated on: Wed, 03 September 2014 03:52]

Report message to a moderator

Re: Find missing sequence number between given range [message #622995 is a reply to message #622993] Wed, 03 September 2014 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try thinking about what people say and what you write.

The row generator gives numbers between 51001 and 51999
The outer query looks for numbers starting with 52.

Plus you have the two queries the wrong way round. You need values from the row generator that aren't in your table.
Re: Find missing sequence number between given range [message #622996 is a reply to message #622994] Wed, 03 September 2014 03:54 Go to previous messageGo to next message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
SELECT CUST_CD, CUST_NAME 
FROM CUST_MST
WHERE CUST_CD LIKE '52%' 
  AND CUST_CD NOT IN
                  (
                    SELECT TO_CHAR(51000 + LEVEL)
                    FROM DUAL
                    CONNECT BY LEVEL <=999
                   )
ORDER BY CUST_CD

Re: Find missing sequence number between given range [message #622997 is a reply to message #622996] Wed, 03 September 2014 03:59 Go to previous message
Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra
Member
I am so sorry. I found my mistake.
Previous Topic: Oracle sql query to pick current and previous records
Next Topic: SQL Query
Goto Forum:
  


Current Time: Fri Apr 26 20:46:28 CDT 2024