Thanks for the question, vishnudas.
Asked: March 17, 2018 - 11:44 am UTC
Last updated: March 19, 2018 - 4:47 am UTC
Version: 11.2.0.1.0
Viewed 1000+ times
You Asked
i have a table
create table demo(ord_no number(4),division varchar2(100));
value for the table is
insert into demo values(1,123456789012);
i just want to create a procedure to do the following
cut the division into 4 in length(means 1234 5678 9012) and display this iterated value with column ord_no 1 like
ORD_NO DIVISION
1 1234
1 5678
1 9012
please suggest a way to solve this..
and Connor said...
SQL> create table demo(ord_no number(4),division varchar2(100));
Table created.
SQL> insert into demo values(1,'123456789012');
1 row created.
SQL>
SQL> select *
2 from demo d,
3 table(cast(multiset(select substr(d.division,rownum*4-3,4) x from dual connect by level <= length(d.division)/4 ) as sys.odcinumberlist));
ORD_NO DIVISION COLUMN_VALUE
---------- ---------------------------------------------------------------------------------------------------- ------------
1 123456789012 1234
1 123456789012 5678
1 123456789012 9012
Search this site for "lateral" for how this is made easier in 12c.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment