Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    15

    Unanswered: Pl/sql insert query result into nested table.

    Hi Guys

    I was wondering if anyone can help inserting an query result retrieve from a nested in to another nested table.

    First 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.

    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)
    ect........

    An select statement 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).

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

    Second table has pretty much the same structure but no value.

    CREATE TABLE old_table ( tDAY DATE, VALUE_thours tt_day );

    I've tried to insert the select statement output into old table using the following but it didn't work

    insert into old_table (day, value_hours) values
    (SELECT to_char(DAY, 'Day') tdate, hours, AVG(data) 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 the select statement along. But how to insert Hours and date in the inner nested table of old_table and into tdate. As you can see the result is what select statement along produced but how to 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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why are you trying to implement nested table when you don't know how to actually use them?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2012
    Posts
    15
    I wish I didn't have to, but is a part of the requirement to use nested table and also insert into a nested table.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >insert into old_table (day, value_hours) values (SELECT to_char(DAY, 'Day') tdate, hours, AVG(data)

    for INSERT statement use of "VALUES" & "SELECT" are mutually exclusive; one or the other; never both

    It would be helpful in the future if you actually posted using COPY & PASTE
    so we can see exactly what you do & how Oracle actually responds
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •