SQL & PL/SQL

Hi Guy

I've created a table with nested table in and inserted some values. note: this is just an example the real table has more values inserted.

REATE OR REPLACE TYPE tt_hours AS OBJECT(hours INTEGER, data NUMBER);
/
CREATE OR REPLACE TYPE tt_day AS VARRAY(7) OF tt_hours;
/
CREATE TABLE old_table
(
DAY DATE,
VALUE_hours tt_day
);
INSERT INTO old_table
(day, value_hours)
VALUES
(TO_DATE('01/06/2012 22:00:34'),
tt_DAY(
tt_hours(1,0.025727),
tt_hours(2,0.012047),
tt_hours(3,0.012857),
tt_hours(4,0.012107),
tt_hours(5,0.012849),
tt_hours(6,0.01215),
tt_hours(7,0.0129)))

I've also created another table with same structure but with no value inserted.

REATE OR REPLACE TYPE yy_hours AS OBJECT(thours INTEGER, tdata NUMBER);

CREATE OR REPLACE TYPE yy_day AS VARRAY(7) OF yy_hours;

CREATE TABLE new_table ( tDAY DATE, VALUE_thours yy_day )

I run a select from statement which workout the average of data from old table by group.

SELECT to_char(DAY, 'Day'), hours, AVG(data)
FROM old_table n, TABLE(n.value_hours) v
GROUP BY to_char(DAY, 'Day'), hours;

How do I insert the result in new_table's ?