Oracle SQL insert into statement ORA-00979

Question

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)

Your question doesn't really make sense. Your suggested table has three columns, but the query returns only two. Sample data would be a big help to the question. — Gordon Linoff
– Gordon Linoff, Commented Jul 28, 2015 at 18:11
@GordonLinoff Yes I know, what I want in the new table the third column mapped to the WHERE of the first query. So the new table has the 2 values the first query returns, as well as a 3rd value which will be what's in the WHERE currently — bbedward
– bbedward, Commented Jul 28, 2015 at 18:14
If you are going from two to three columns you will need to provide a value for the new column. To make it easy fill it with a default value and go back and update the value after copying the table. — Nathan
– Nathan, Commented Jul 28, 2015 at 18:16

Ditto · Accepted Answer · 2015-07-28 18:14:44Z

3

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 :( )

answered Jul 28, 2015 at 18:14

Ditto

3,3693 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ditto Over a year ago

Ok, I've read it ... seems like what I put is still the solution for you ? have you tried it ? If it doesn't work please be sure to show what it does/doesn't do, what you want, and why you want that.

Vamsi Prabhala · Accepted Answer · 2015-07-28 18:24:28Z

1

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

edited Jul 28, 2015 at 18:24

answered Jul 28, 2015 at 18:14

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Collectives™ on Stack Overflow

Oracle SQL insert into statement ORA-00979

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related