Home » SQL & PL/SQL » SQL & PL/SQL » Finding missing sequences
Finding missing sequences [message #227298] Tue, 27 March 2007 22:51 Go to next message
jiteshmnair
Messages: 12
Registered: April 2006
Location: Mumbai
Junior Member

I am facing a problem of missing sequences in my invoice table. You see we have got distributed databases. The data merges from all these databases to the HO Central Database. Now many times some of the entries get skipped. We really do not know what the transactions numbers. We only take the first and last invoice numbers of that month from the users. The invoice number is of VARCHAR type. Now i want to generate the missing numbers between the first and last invoice id.
Please help me out on this as how can i solve this problem.

Example: -
Invoice_id
------------
26SI6D0001
26SI6D0002
26SI6D0003
26SI6D0005
26SI6D0006
26SI6D0007
26SI6D0008
26SI6D0010

In the above the invoice id's 26SI6D0004 & 26SI6D0009 are missing. Now how can i find this numbers by SQL.

Pleae give your expert opinions on this.

Thank You,
Jitesh M Nair
Re: Finding missing sequences [message #227300 is a reply to message #227298] Tue, 27 March 2007 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Now how can i find this numbers by SQL.
First generate a list of all possible/valid "numbers" & then subtract the ones that get logged.
Re: Finding missing sequences [message #227318 is a reply to message #227300] Wed, 28 March 2007 00:13 Go to previous messageGo to next message
jiteshmnair
Messages: 12
Registered: April 2006
Location: Mumbai
Junior Member

Thanks for the reply...

If u can be more specific, i mean how to go about it??

Can u send me the sqls for the same....

The table name is invoice and the column name is invoice_id
Re: Finding missing sequences [message #227319 is a reply to message #227298] Wed, 28 March 2007 00:21 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Can u send me the sqls for the same....
Are you gettng paid to produce the SQL?
If so, should I get paid for doing the same by you?
It appears to me you are unwilling or unable to simply search for previousy posted solutions.
You're On Your Own (YOYO)!
Re: Finding missing sequences [message #227322 is a reply to message #227298] Wed, 28 March 2007 00:35 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Hi
jeteshmnair

The following is the table structure i used

create table asher( num varchar(15));
insert into asher values('26SI6D0001');
insert into asher values('26SI6D0002');
insert into asher values('26SI6D0003');
insert into asher values('26SI6D0005');
insert into asher values('26SI6D0006');
insert into asher values('26SI6D0007');
insert into asher values('26SI6D0008');
insert into asher values('26SI6D0010');



now the follwowing code will give you the values that are missing

with tab as
(select to_number(replace(num,'26SI6D')) su from asher)
select fs from (select '26SI6D'||to_char(lpad(rownum,4,0)) fs from dba_tables  where rownum<=(select max(su) from tab))
where fs not in (select num from asher)




SQL> ed
Wrote file afiedt.buf

  1  with tab as
  2  (select to_number(replace(num,'26SI6D')) su from asher)
  3  select fs from (select '26SI6D'||to_char(lpad(rownum,4,0)) fs from dba_tabl
es  where rownum<=(select max(su) from tab))
  4* where fs not in (select num from asher)
SQL> /

FS
----------
26SI6D0004
26SI6D0009



i used db_tables because i dont have any other table which has rows more than table "asher", Embarassed
so only dba_table will generate enough rownums. Razz

[Updated on: Wed, 28 March 2007 00:36]

Report message to a moderator

Re: Finding missing sequences [message #227327 is a reply to message #227322] Wed, 28 March 2007 00:57 Go to previous messageGo to next message
jiteshmnair
Messages: 12
Registered: April 2006
Location: Mumbai
Junior Member

anacedent:
I am not getting paid for the SQL. I am working in that organization. And i referred the earlier SQL's but could not find the solution which i needed.

asherisfine:
Thank You sir, It worked.... U really made my day....

Thank you once again
Re: Finding missing sequences [message #227328 is a reply to message #227298] Wed, 28 March 2007 00:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
asherisfine,
I am glad to see that you have volunteered to supply jeteshmnair with an ongoing solution to his missing invoice number problem.
With you providing the solution now & forward jeteshmnair won't have to return with future similar requests.

Thanks!
Re: Finding missing sequences [message #227334 is a reply to message #227328] Wed, 28 March 2007 01:06 Go to previous messageGo to next message
jiteshmnair
Messages: 12
Registered: April 2006
Location: Mumbai
Junior Member

anacedent:
Do not worry i face many problems, and i will come time and again to you people for your expert opinions..
Re: Finding missing sequences [message #227337 is a reply to message #227322] Wed, 28 March 2007 01:16 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

see this query

select '26SI6D'||lpad(su,4,0) from (
(select level as su from dual connect by level<=10
minus
select to_number(replace(num,'26SI6D')) su from asher))

Thanks
srinivas
Re: Finding missing sequences [message #227363 is a reply to message #227337] Wed, 28 March 2007 01:52 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
see this
select * from (select fs from (select '26SI6D'||lpad(rownum,4,0) fs from dict)
where fs<=(select max(num) from asher))
where fs not in (select num from asher)
Re: Finding missing sequences [message #227365 is a reply to message #227363] Wed, 28 March 2007 01:54 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry for trobule

we dont know starting value.

how is its possile?

thanks,
srinivas

Re: Finding missing sequences [message #227370 is a reply to message #227365] Wed, 28 March 2007 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"min" isn't the start?

Regards
Michel
Re: Finding missing sequences [message #227371 is a reply to message #227370] Wed, 28 March 2007 02:06 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
asume that
26SI6D0003
26SI6D0005
26SI6D0006
26SI6D0007
26SI6D0008
26SI6D0010


what time
output is
26SI6D0009
Re: Finding missing sequences [message #227384 is a reply to message #227371] Wed, 28 March 2007 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And so?
use rownum+min-1 or level+min-1 instead of rownum or level, and max-min+1 instead of max in the previous queries.

Michel
Re: Finding missing sequences [message #227387 is a reply to message #227371] Wed, 28 March 2007 02:26 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry

asume that
26SI6D0003
26SI6D0005
26SI6D0006
26SI6D0007
26SI6D0008
26SI6D0010


what time
output is
26SI6D0004
26SI6D0009
Re: Finding missing sequences [message #227392 is a reply to message #227387] Wed, 28 March 2007 02:32 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
Hi All

see this its prefect query

select * from (select fs from (select '26SI6D'||lpad(rownum,4,0) as fs from dual connect by level<=100000)
where fs<=(select max(num) from srinu11)) where fs not in (select num from srinu11)

minus

select * from (select fs from (select '26SI6D'||lpad(rownum,4,0) as fs from dual connect by level<=100000)
where fs<=(select min(num) from srinu11)) where fs not in (select num from srinu11)

thanks
srinivas
Re: Finding missing sequences [message #227395 is a reply to message #227392] Wed, 28 March 2007 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try it?
I hope you are not in a hurry and have a lot of memory.
"connect by level <= 100000" Ouch!

Regards
Michel

[Updated on: Wed, 28 March 2007 02:43]

Report message to a moderator

Re: Finding missing sequences [message #227396 is a reply to message #227395] Wed, 28 March 2007 02:41 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
yes i tried

its working prefect

thanks,
srinivas
Re: Finding missing sequences [message #227397 is a reply to message #227395] Wed, 28 March 2007 02:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Wed, 28 March 2007 09:39
Did you try it?
I hope you are not in a hurry and have a lot of memory.
"connect by level <= 100000" Ouch!
Is it really that bad. If it was connect by CUBE, i'd understand, but connect by level from dual?

MHE
Re: Finding missing sequences [message #227399 is a reply to message #227397] Wed, 28 March 2007 02:50 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
u can use level<10
now its ok
thanks
srinivas
Re: Finding missing sequences [message #227400 is a reply to message #227399] Wed, 28 March 2007 02:51 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
otherwise u can use dict or dbms_tables
thanks
srinivas
Re: Finding missing sequences [message #227408 is a reply to message #227384] Wed, 28 March 2007 03:19 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
plz send me all query.

thanks
srinivas
Re: Finding missing sequences [message #227415 is a reply to message #227397] Wed, 28 March 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is it really that bad.

Seems not so bad after all. Embarassed

Regards
Michel
Re: Finding missing sequences [message #227419 is a reply to message #227298] Wed, 28 March 2007 03:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
"Michel Cadot wrote on Wed, 28 March 2007 09:39"

Did you try it?
I hope you are not in a hurry and have a lot of memory.
"connect by level <= 100000" Ouch!


At least it is not necessary when you take only last four digits from the number. 9999 as a limit should be enough.

Personally I would use TO_CHAR with proper format instead of LPAD on numeric value (and rely on implicit conversion). However, if it works...

[Edit: Added this paragraph]
select '26SI6D'||to_char(lpad(rownum,4,0)) fs from <...>

If I remember well, after a standard conversion to varchar, the number contains heading space. The column would contain values '26SI6D00 1', '26SI6D00 2', ..., '26SI6D9999', so it would not 'work'. With the enhanced limit, the sequence will continue with '26SI6D0000', '26SI6D0001' and so on, so it would start 'working'.

On the other hand, if using
select '26SI6D'||to_char(rownum,'fm0999') fs from <...>

(which I would prefer) the limit shall not exceed 9999.

[Updated on: Wed, 28 March 2007 04:12]

Report message to a moderator

Re: Finding missing sequences [message #227433 is a reply to message #227419] Wed, 28 March 2007 04:14 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
plz see the screen shot
  • Attachment: seq1.doc
    (Size: 73.50KB, Downloaded 361 times)
Re: Finding missing sequences [message #227508 is a reply to message #227322] Wed, 28 March 2007 07:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
asherisfine wrote on Wed, 28 March 2007 01:35

  1  with tab as
  2  (select to_number(replace(num,'26SI6D')) su from asher)
  3  select fs from (select '26SI6D'||to_char(lpad(rownum,4,0)) fs from dba_tabl
es  where rownum<=(select max(su) from tab))
  4* where fs not in (select num from asher)




This assumes the invoice_id sequence number starts at 0001 and if it doesn't start at 0001, the query will actually return records back from 0001 to the lowest invoice_id in addition to the filling in the gap values.

A slightly more performant query that doesn't rely on a dictionary table and only fills in the gap between the range of low & high invoice_id:

select * from (
	select '26SI6D'||to_char(mn + level -1, 'fm0999') val
	from (
		select min(replace(invoice_id, '26SI6D')) mn
			, (max(replace(invoice_id, '26SI6D')) - min(replace(invoice_id, '26SI6D'))) btwn
		from inv)
	connect by level <= btwn + 1)
where val not in (select invoice_id from inv);


I agree with flyboy's use of TO_CHAR, however I believe it is a moot point in this case.
Re: Finding missing sequences [message #227531 is a reply to message #227508] Wed, 28 March 2007 08:37 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
Excellent!!!

i wrote only 10 records base. that time i dont check lachs of
records

thanks,
srinivas

Re: Finding missing sequences [message #227534 is a reply to message #227531] Wed, 28 March 2007 08:53 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
see this query is working

but it is complex.

select * from (select fs from (select '26SI6D'||to_char((rownum+1-1),'fm009999') as fs
from dual connect by level<=100000)
where fs<=(select max(num) from srinu111)) where fs not in (select num from srinu111)

minus

select * from (select fs from (select '26SI6D'||to_char((rownum+1-1),'fm009999') as fs
from dual connect by level<=100000)
where fs<=(select min(num) from srinu111)) where fs not in (select num from srinu111)
Re: Finding missing sequences [message #227702 is a reply to message #227534] Thu, 29 March 2007 02:10 Go to previous messageGo to next message
jiteshmnair
Messages: 12
Registered: April 2006
Location: Mumbai
Junior Member

Thanks for all the SQL's provided. They are working. But considering the scenarion wherein my first five invoice characters keeps changing as per the month and locatiom.

for ex:
For April it is 26SI6D001
For May it is 26SI6E001
For June it is 26SIFA001


Now in this scenario a script will has to be created where search will be based on a month basis i.e say 01-apr-2006 to 30-apr-2006.

I think you people have got my question??
Re: Finding missing sequences [message #227711 is a reply to message #227702] Thu, 29 March 2007 02:39 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
no iam not getting clearly.

plz send me script and what tell me what is u's requirement.

thanks,
srinivas
Re: Finding missing sequences [message #227789 is a reply to message #227711] Thu, 29 March 2007 07:25 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Suppose, it is compound from prefix '26SI' plus '6' for the year's last digit (what about 2010 and after?) plus letter for month (from January 'A' to December 'L') plus three digits (001 to 999). Then simple extraction of these values from input date solves it:
SELECT '26SI' -- prefix
     ||substr(to_char(input_date, 'YYYY'), 4, 1) -- year
     ||chr(to_number(to_char(input_date, 'MM'))+64) -- month
     ||to_char(mn + level -1, 'fm099') val

This will work only for one month, so only one input date from that month is sufficient.
Re: Finding missing sequences [message #227872 is a reply to message #227789] Thu, 29 March 2007 22:47 Go to previous messageGo to next message
jiteshmnair
Messages: 12
Registered: April 2006
Location: Mumbai
Junior Member

flyboy:

Ok take it this way the invoice no starts
with
26 (location)
SI (sales invoice)
06 (year)
0001 (number)
26SI060001

Now how can you find the sequences? I have converted into a much simpler form...

Thanks
Re: Finding missing sequences [message #227876 is a reply to message #227298] Thu, 29 March 2007 23:11 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Thanks for all the SQL's provided. They are working.

Quote:
Now how can you find the sequences?

Use the SQL which 'worked', only replace the value with the expression I provided (or modify it by your demands as you slightly changed them).
I would prefer to use the ebrian's solution.
Re: Finding missing sequences [message #227878 is a reply to message #227298] Thu, 29 March 2007 23:21 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
This might get you close to what you desire.
set pages 0
select'26SI06'||rownum from dual
where rownum < 100010
connect by 0=0
/
Re: Finding missing sequences [message #227881 is a reply to message #227878] Fri, 30 March 2007 00:22 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
anacedent wrote on Fri, 30 March 2007 00:21
This might get you close to what you desire.
set pages 0
select'26SI06'||rownum from dual
where rownum < 100010
connect by 0=0
/


Slight modification. May want to rewrite this as below, otherwise your memory usage will go thru the roof.

select'26SI06'||rownum from dual
connect by 0=0
and rownum < 100010
/
Re: Finding missing sequences [message #227888 is a reply to message #227881] Fri, 30 March 2007 01:04 Go to previous messageGo to next message
jiteshmnair
Messages: 12
Registered: April 2006
Location: Mumbai
Junior Member

anacedent:

Greate it worked the earlier was taking a bit of time. This came quickly. But can you tell me how will we generate the number
as
26SI060001.
Now the result we are getting is
26SI061.
Re: Finding missing sequences [message #227892 is a reply to message #227888] Fri, 30 March 2007 01:18 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
select'26SI06'||to_char((rownum+1-1),'fm0999' )from dual
connect by 0=0
and rownum < 100010

Thanks
srinivas
Re: Finding missing sequences [message #227893 is a reply to message #227298] Fri, 30 March 2007 01:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You're On Your Own (YOYO)!

[Updated on: Fri, 30 March 2007 01:20] by Moderator

Report message to a moderator

Re: Finding missing sequences [message #227918 is a reply to message #227893] Fri, 30 March 2007 03:16 Go to previous messageGo to next message
jiteshmnair
Messages: 12
Registered: April 2006
Location: Mumbai
Junior Member

anacedent:

Sorry for the trouble, but I am more of into ERP Implementation and Oracle Backup/Recovery/Tuning part. That's all. I am not too good at building queries.

srinivas

select '26SI06'||to_char((rownum+1-1),'fm0999' ) from dual
and rownum < 100010 and '26SI06'||to_char((rownum+1-1),'fm0999' )
not in
(select invoice_id from invoice where invoice_id betwee '26SI060001' and '26SI060100)
connect by 0=0
;

The result I get is
26SI060001

now the above id is already there in the invoice table

what i result i want is only missing numbers displayed.
26SI060004
26SI060009
Re: Finding missing sequences [message #227941 is a reply to message #227298] Fri, 30 March 2007 05:35 Go to previous messageGo to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
jiteshmnair,
sorry, but I did not totally get, what you want.

First you ask for solution of a problem, get some solutions (better or worse), claim them working. Then ask only for a little format change of the stored column (as I understood) or for explanation of the way how rows are generated in the subquery containing CONNECT BY section (as others tried to show you).
SELECT '26SI06'||to_char((rownum+1-1),'fm0999' )
FROM dual
CONNECT BY rownum < 100010;

What does this code return to you? I do not have an access to Oracle, but I suspect it throws something like 'numeric overflow', as I guessed in my previous post. You can fix it when changing the last number from 100010 to 9999.

My advice stays the same: take the ebrian's solution and replace '26SI6D' with the prefix, you decided for, everywhere in the query (seems to be '26SI06' now).

By the way I do not believe the query you posted returned anything as it contains some syntax errors.
Previous Topic: count difference between tuples
Next Topic: Day Count (no: of days between 2 dates)
Goto Forum:
  


Current Time: Sun Dec 04 04:20:10 CST 2016

Total time taken to generate the page: 0.05127 seconds