PL/SQL find values within nested tables

Question

Hi I have problem I don't know if anyone can help. I've created nested tables and inserted values in in a table.

CREATE 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 NEM_RM16
(
  DAY                 DATE,
  VALUE_hours         tt_day
 );
INSERT INTO NEM_RM16
   (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)));

So there will be 30 rows inserted in new_table each representing 1 day of the month in this month (June). the above is a example of first row with day = 01/06/2012 with 8 hours of data.

How do I write a program to find all the average hour all specific day e.g average of all first hour of all Mondays in the table (god I hope I'm making sense).

the result should be 7 rows (7 days in a week) with

tt_hours (1, average over the month)
tt_hours (2, average over the month)
..........

Second table:

 CREATE TABLE old_table ( tDAY DATE, VALUE_thours tt_day );

I've tried to insert using the following. To insert your result in the old table but did not work.

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

e.g the old_table should have the following data in Friday after running your select statement. Hours and date are in the nested table and tdate is DATE type. As you can see the result is what your select statement produced but is inserted into a same structured table.

  tdate         HOURS        data 
  ----------- ---------- ----------
  Friday               1   0,025727
  Friday               2   0,012047
  Friday               3   0,012857
  Friday               4   0,012107
  Friday               5   0,012849
  Friday               6    0,01215
  Friday               7     0,0129

Answer 1

You can use a lateral join to retrieve the values from the nested tables:

SQL> select n.day, v.hours, v.data from NEM_RM16 n, table(n.value_hours) v;

DAY              HOURS       DATA
----------- ---------- ----------
01/06/2012           1   0,025727
01/06/2012           2   0,012047
01/06/2012           3   0,012857
01/06/2012           4   0,012107
01/06/2012           5   0,012849
01/06/2012           6    0,01215
01/06/2012           7     0,0129

I'm not sure I really understand your question correctly but from the above query you can run any aggregation, such as:

SQL> SELECT to_char(DAY, 'Day') day_of_week, hours, AVG(data) avg_data
  2    FROM NEM_RM16 n, TABLE(n.value_hours) v
  3   GROUP BY to_char(DAY, 'Day'), hours;

DAY_OF_WEEK      HOURS   AVG_DATA
----------- ---------- ----------
Friday               1   0,025727
Friday               2   0,012047
Friday               3   0,012857
Friday               4   0,012107
Friday               5   0,012849
Friday               6    0,01215
Friday               7     0,0129

Use COLLECT if you want to reconstruct a nested table, e.g:

SELECT day_of_week, CAST(COLLECT(tt_hours(hours, avg_data)) AS tt_day)
  FROM (SELECT to_char(DAY, 'Day') day_of_week, hours, AVG(data) avg_data
          FROM NEM_RM16 n, TABLE(n.value_hours) v
         GROUP BY to_char(DAY, 'Day'), hours)
 GROUP BY day_of_week