CODEWITHSUNDEEP
sqloracle-databaseinsert
bbedward
bbedward

Reputation: 6478

Oracle SQL insert into statement ORA-00979

I have a query below:

SELECT count(*) AS my_table_count
    ,my_table_date_value
FROM my_table
WHERE my_table_value = 500
GROUP BY my_table_date_value

This returns an array/two column table of the count and the date where the value is 500. Which is what I want.

However, i created a table like this for the results of the above query:

CREATE TABLE my_new_table (
 my_table_count NUMBER(10)
,my_table_date_value DATE
,my_table_value NUMBER(38)
)

What I want to do is fill the new table with all of the data from the first query, where my_table_value > 0

I tried using insert into (select...), but I'm not sure how to populate the my_table_value that way. I'm thinking iterating from 1 to the max(my_table_value) would do it but i'm not sure how to write this in Oracle SQL.

The result I want is where I can query my_new_table instead of using the first query as following:

SELECT my_table_count
      ,my_table_date_value
FROM my_new_table
WHERE my_table_value = 500;

EDIT

Here's what I do now for one value (say 500). All I want to do is go back and get the values 0-499 in the table.

 SET my_value = 500;
 SET sqlString = 'select COUNT(*) AS MY_COUNT,MY_DATE from MY_TABLE where MY_VALUE=? group by MY_DATE';
        SET rows[] = <query result, with my_value = 500>
        DECLARE index INTEGER 1;
        FOR result as r.rows[] DO
            SET count = result.MY_COUNT;
            SET my_date = result.MY_DATE;
            insert into MY_NEW_TABLE(MY_COUNT,MY_DATE,MY_VALUE)
            VALUES(count,my_date,my_value);
            SET index = index + 1;
        END FOR;

All I want to do is modify this, so it inserts everything from 1 to max(my_value)

Upvotes: 0

Views: 615

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use create table as select for this. This way you can add your new columns to the new table. Also, you can alter the table should something change.

create table my_new_table as
select count(*) as my_table_count,my_table_date_value,my_table_value
from my_table 
where my_table_value = 500 --add more conditions when needed
group by my_table_date_value

Upvotes: 1

Ditto
Ditto

Reputation: 3344

I'm not entirely certain this is what you're after, but it sounds like you want to grab all the rough data into your "my_new_table" and then sift through it later ..

  insert into my_new_table
     ( my_table_count,
       my_table_date_value,
       my_table_value )
  select count(*) as my_table_count,
           my_table_date_value ,
           my_table_value
    from my_table 
   where my_table_value > 0
   group by my_table_date_value,
            my_table_value

(Note: untested, since no sample data, no create scripts, etc .. ie nothing to go on :( )

Upvotes: 3

Related Questions