| Concatenation text issue [message #374332] |
Wed, 06 June 2001 10:07  |
Toyn
Messages: 36
Registered: April 2001
|
Member |
|
|
I have a text field in which each line of text is inserted into a new row. These rows of text are all of the same report, and all have the same accession number. For each new line of text a new row is created, with the text field being populated with the new text, and the accession fields being populated with the same accession number of the report.
(i.e. acc1 'hello' <next row> acc1 'how are you?'). I would like to concatenate these rows to read 'hello how are you?' into a single row and then export that row into another table. I haven't had much success with concatenation operators. Any help would be appreciated. Thanks.
|
|
|
|
| Re: Concatenation text issue [message #374336 is a reply to message #374332] |
Wed, 06 June 2001 10:41  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
A plsql block can do this....
again with concatenation only.......
declare
l_multi_line_text varchar2(100);
l_single_line_text varchar2(1000);
l_accNum varchar2(10);
--select the accession_number from the second table where you want load text in one string
cursor c1 is select accession_number from t2;
-- select the text from your first tables for a particular accession number(report)
cursor c2(p_accNum varchar2) is select accession_text from t1;
begin
-- first get the accession_number one by one from your second table
for r1 in c1 loop
exit when c1%notfound;
l_accNum := r1.accession_number;
--pass it on to the first table and merge the text into single line for that particular acc_num
open c2(l_accNum);
loop
fetch accession_text into l_multi_line_text;
l_single_line_text := l_single_line_text || l_multi_line_text;
end loop;
close c2;
--now update your second table with sing line of text
update t2 set accession_text = l_single_line_text
where accession_number = l_accNum;
end loop;
end;
/
modify the above plsql block and run it......
Bala.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
