Home » SQL & PL/SQL » SQL & PL/SQL » Sql Filter (Oracle 10g)
Sql Filter [message #390921] Tue, 10 March 2009 05:41 Go to next message
Messages: 1
Registered: March 2009
Location: Nairobi, kenya
Junior Member

Hello Good Sql pals,
Lets say I have like 3000 records or receipts arranged in order of receipt number e.g. receipt 20001 to receipt 23001.
Now receipt 22991, receipt 21222 and receipt 22456 are missing. How can I write a Sql query that will pick out the missing receipts?
Or how will I query the points where the sequence is broken?
Re: Sql Filter [message #390923 is a reply to message #390921] Tue, 10 March 2009 05:46 Go to previous messageGo to next message
Messages: 3202
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could use the lead() or lag() analytic functions.

Re: Sql Filter [message #390924 is a reply to message #390921] Tue, 10 March 2009 05:47 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Option 1) Use a row generator to generate a complete list of sequence numbers, and outer join/minus this list with the numbers you actually have

Option 2) Use the Lead analytic functions to compare the next number in the list to the current number +1.
Previous Topic: how to use migrate script
Next Topic: Sql Query
Goto Forum:

Current Time: Thu Aug 17 22:18:04 CDT 2017

Total time taken to generate the page: 0.10898 seconds