Home » SQL & PL/SQL » SQL & PL/SQL » sequence into individual records (Oracle 10g and Windws)
sequence into individual records [message #444618] Tue, 23 February 2010 01:33 Go to next message
aijaz786
Messages: 91
Registered: February 2010
Member

create table x (
field1 varchar2(10) );

insert into x values ('001-002');
insert into x value ('0150-0152');
insert into x value ('0100-0101');

create table y (
field varchar2(6));


recortd in table y


001
002
0150
0151
0152
0100
0101


any help to achieve the above.

Thanks



Re: sequence into individual records [message #444621 is a reply to message #444618] Tue, 23 February 2010 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post a WORKING test case:
SQL> insert into x value ('0150-0152');
insert into x value ('0150-0152')
                     *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> with 
  2    lines as (select level line from dual connect by level <= 10),
  3    data as (
  4      select to_number(substr(field1,1,instr(field1,'-')-1)) v1,
  5             to_number(substr(field1,instr(field1,'-')+1)) v2
  6      from x
  7    )
  8  select to_char(v1+line-1,'0000') val
  9  from data, lines
 10  where line <= v2-v1+1
 11  order by 1
 12  /
VAL
-----
 0001
 0002
 0100
 0101
 0150
 0151
 0152

7 rows selected.

Regards
Michel

[Updated on: Tue, 23 February 2010 01:46]

Report message to a moderator

Re: sequence into individual records [message #444623 is a reply to message #444618] Tue, 23 February 2010 01:52 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Use Instr/substr
Re: sequence into individual records [message #444624 is a reply to message #444618] Tue, 23 February 2010 01:55 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi Michael and guys,

My mistake. x table sructure is

field1 varchar2(20),
range_inital varchar2(6),
range_final varchar2(6))

insert into x values('abcdc','0001','0002');
insert into x values('xybbb','0099','0100');

create table y(
field1 varchar2(20),
seq_number_assigned varchar2(6));

data in y like:

filed1 seq_number_assigned

abcdc 0001
abcdc 0002
xybbb 0099
xybb 0100


Thanks.

Aijaz



Re: sequence into individual records [message #444628 is a reply to message #444624] Tue, 23 February 2010 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it with what I provided.

Regards
Michel
Re: sequence into individual records [message #444629 is a reply to message #444618] Tue, 23 February 2010 02:17 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi,

I am unable to transform it.

Thanks.
Re: sequence into individual records [message #444630 is a reply to message #444629] Tue, 23 February 2010 02:24 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
aijaz786 wrote on Tue, 23 February 2010 09:17
I am unable to transform it.


Since you don't tell us why, we have to start troubleshooting at the beginning:

1) Is the computer turned on?


Re: sequence into individual records [message #444631 is a reply to message #444618] Tue, 23 February 2010 02:25 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
my mistake, Sir.
Re: sequence into individual records [message #444633 is a reply to message #444629] Tue, 23 February 2010 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
aijaz786 wrote on Tue, 23 February 2010 09:17
Hi,

I am unable to transform it.

Thanks.

Show us what you tried.

Regards
Michel

Re: sequence into individual records [message #444634 is a reply to message #444618] Tue, 23 February 2010 02:34 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi Michael,

I tried to trasnformyou previous script but it did not work. That is why I posted.

Re: sequence into individual records [message #444635 is a reply to message #444634] Tue, 23 February 2010 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Show us what you tried.
The "data" purpose was to break you column into 2, now you have 2 columns in your table so you have not to do this step.
The rest is the same.

Regards
Michel

[Updated on: Tue, 23 February 2010 02:39]

Report message to a moderator

Re: sequence into individual records [message #444642 is a reply to message #444618] Tue, 23 February 2010 03:30 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
with
lines as (select level line from dual connect by level <= 10),
data as (
select field1,
to_number(range_inital) v1,
to_number(range_final) v2
from x
)
select field1,to_char(v1+line-1,'0000') val
from data, lines
where line <= v2-v1+1
order by 1

but how can make level <= 10,it limites the records it should be dynamic
Re: sequence into individual records [message #444645 is a reply to message #444642] Tue, 23 February 2010 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make it equal to the max difference between initial and final.

Regards
Michel
Re: sequence into individual records [message #444651 is a reply to message #444618] Tue, 23 February 2010 04:10 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
select nvl(to_number(number_to),0)-nvl(to_number(number_from),0) from table_name (this run o.k.)

when I use max with this query, I get invalid number error (ORA-01722) like


select max(nvl(to_number(number_to),0)-nvl(to_number(number_from),0)) from table_name
Re: sequence into individual records [message #444655 is a reply to message #444651] Tue, 23 February 2010 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the whole query.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: sequence into individual records [message #444657 is a reply to message #444651] Tue, 23 February 2010 05:30 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
How many rows did the first query return? Did you see all of them? In TOAD or SQL Developer, only a few rows are fetched and shown. You might be lucky they were correct.

The second query has to fetch all rows. Some of them did not contain number in NUMBER_FROM or NUMBER_TO. You can easily find it e.g. with this query:
select *
from table_name
where length( translate(number_from, 'A-0123456789', 'A' ) ) > 0
   or length( translate(number_to, 'A-0123456789', 'A' ) ) > 0
(not tested)

Conclusion: storing NUMBERs in VARCHAR2 column is not a good idea at all.
Previous Topic: How to implement Trigger
Next Topic: sql query
Goto Forum:
  


Current Time: Sun Sep 25 16:18:10 CDT 2016

Total time taken to generate the page: 0.14151 seconds