Oracle SQL: how to insert a SELECT statement with a GROUP BY clause in a table with an IDENTITY column?

Question

In the application, I intend to trim and paste into the Oracle 12c database, but found this problem in the IDENTITY column. Despite the fact that the INSERT... SELECT works in most SELECT applications that I tried when this statement also has a GROUP BY , it does not work, giving the complaint "ORA-00979: not a GROUP BY ". The following is sample code:

 create table aux ( owner_name varchar2(20), pet varchar2(20) ); insert into aux values ('Scott', 'dog'); insert into aux values ('Mike', 'dog'); insert into aux values ('Mike', 'cat'); insert into aux values ('John', 'turtle'); create table T1 ( id number generated always as identity, owner_name varchar2(20), pet_count number ); insert into T1 (owner_name, pet_count) select owner_name, count(*) as pet_count from aux group by owner_name; select owner_name, count(*) as pet_count from aux group by owner_name; 

It works with the first insert, but does not work on the next.

EDIT: I changed the code, so the problem is clear, but still reproducible.

Appreciate the help!

Answer 1

The Oracle Community has answered this question. https://community.oracle.com/message/13227544#13227544

 insert into T1 (owner_name, pet_count) with t as (select /*+ materialize */ owner_name, count(*) as pet_count from aux group by owner_name) select owner_name, pet_count from t 

When expressing the original answer, keep in mind that the materialization hint is not documented. Thank you all for your help!