Re: PL/SQL: How copy from several detail records into several fields in a single summary record?

From: conan <"conan">
Date: 14 Jan 1999 23:59:12 GMT
Message-ID: <01be4019$44824260$35f5869f_at_default>

I'll leave the outer cursor on the main table to yourselve once the cursor is open the following code should do it declare cursor c1 is

	select  id_column,sequence_number, detail_value
 		from detail_table
		where id_column = main_cursor.id_column;

c1rec c1%type;

det1 c1rec.detail_value%type := null;
det2 c1rec.detail_value%type :=null;
det3 c1rec.detail_value%type :=null;
det4 c1rec.detail_value%type :=null;
det5 c1rec.detail_value%type :=null;
 

begin
open c1;
do until c1%notfound loop
fetch c1 into c1rec;

	if c1rec.sequence_number = 1 then det1 := c1rec.detail_value; end if;
	if c1rec.sequence_number = 2 then det2 := c1rec.detail_value; end if;
	if c1rec.sequence_number = 3 then det3 := c1rec.detail_value; end if;
	if c1rec.sequence_number = 4 then det4 := c1rec.detail_value;end if;
	if c1rec.sequence_number = 5 then det5 := c1rec.detail_value;end if;
end loop;
	insert into OUTPUT_TABLE( id_column,

detail_value_1,detail_value_2,detail_value_3, detail_value_4, detail_value_5)
 values(c1rec.id_column,det1,det2,det3,det4,det5); close c1;
end;  

I'm not sure if the whole of the code above is correct but it should put you on the right track.

Conan

John Haskins <76054.334_at_compuserve.com> wrote in article <77k0ki$qp9$1_at_news-1.news.gte.net>...
> I want to write a PL/SQL script that takes records in this format
(stripped
> to relevant columns):
>
> MASTER_TABLE
> id_column
>
> DETAIL_TABLE (up to 5 records possible per master record)
> id_column
> sequence_number
> detail_value
>
> ...and produces a denormalized output table in this format:
> OUTPUT_TABLE
> id_column
> detail_value_1
> detail_value_2
> detail_value_3
> detail_value_4
> detail_value_5
>
> I'm familiar with writing stored procedures with cursors to select data
out
> of a table and insert it into another table. What I don't know how to do
is
> populate a series of columns in a single row using one column in a set of
> source records. Can anyone point me in the right direction?
>
> The environment is Oracle 7.2.2. The destination columns will represent 5
> out of 120 columns in the output table.
>
> Thanks very much.
>
>
>
>
Received on Fri Jan 15 1999 - 00:59:12 CET