| Substr a column [message #38056] |
Fri, 15 March 2002 16:40  |
KK
Messages: 24
Registered: March 2002
|
Junior Member |
|
|
Hi!
I have a column that contains data as follows
table1.division = 101010111012...1014
which is the dept_number
dept_number = 1010
dept_number = 1011
dept_number = 1012
...
dept_number = 1014
I need to loop through this column(table1.division), join it to - table2.dept_number for every dept_number in table1.division.
Each division will contain multiple departments, max 600 char. There are also multiple divisions.
Any help would be appreciated!! Thanks!!
|
|
|
|
| Re: Substr a column [message #38062 is a reply to message #38056] |
Sun, 17 March 2002 23:23  |
Pratibha
Messages: 29
Registered: November 2001
|
Junior Member |
|
|
Hi
I have given a sample code, make changes according to your requirement, u can use the same logic.
try it out & let me know
-- This sample code uses emp table
-- Begin Code ---------------------------
declare
i number(5);
j number(5);
m_division varchar2(600);
m_cnt number(5);
m_division_after_split number(5);
w_rec emp%rowtype;
begin
j := 1;
m_division := '101020201030202020202020021010101010101010101010101099203040';
m_cnt := length(m_division);
for i in 1 .. m_cnt
loop
select substr(m_division,j,2) into m_division_after_split from dual;
dbms_output.put_line ('Main Loop Cnt: ' || i);
j := j+2;
dbms_output.put_line('Division After Splitting : '|| m_division_after_split);
for w_rec in (
select empno,deptno,ename from emp where deptno = m_division_after_split)
loop
dbms_output.put_line ('Inner Loop Cnt: ' || i);
dbms_output.put_line('empno : '|| w_rec.empno);
dbms_output.put_line('deptno : '|| w_rec.deptno);
dbms_output.put_line('ename : '|| w_rec.ename);
end loop;
end loop; -- main loop end
end;
-- End Code ----------------------------
good luck
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
