Valid GROUP BY query doesn't work when combined with INSERT INTO on Oracle

Question

I'm trying to write an INSERT INTO that does a some DISTINCT/GROUP BY work. The query runs perfectly fine as a select statement, but will not work if it's wrapped into an INSERT INTO.

INSERT INTO MasterRecords
  (BatchRecordRecordID, SourceID, BatchID)
SELECT RecordID, SourceID, BatchID
FROM (
    SELECT RecordID, BatchID, 101 AS SourceID
    FROM BatchRecords
    WHERE BatchID = 150
    GROUP BY RecordID, BatchID
) BR

This earns me:

SQL Error: ORA-00979: not a GROUP BY expression

But if I remove just the INSERT INTO code, it runs perfectly fine:

SELECT RecordID, SourceID, BatchID
FROM (
    SELECT RecordID, BatchID, 101 AS SourceID
    FROM BatchRecords
    WHERE BatchID = 150
    GROUP BY RecordID, BatchID
) BR

Results:

3   101 150
5   101 150
6   101 150
2   101 150
4   101 150
8   101 150
7   101 150
1   101 150

My assumption is that GROUP BY's are not allowed inside INSERT INTO select statements but I can find almost no documentation confirming this.

Answer 1

I arrived here trying to solve a similar situation so it seems to me that this kind of problem still appears.

In my case, avoiding any optimizer transformation, did the trick.

I applied a NO_QUERY_TRANSFORMATION hint to the "intoed" SELECT statement and the error disappeared.

In the case of this question, I should rewrite it as:

INSERT INTO MasterRecords
  (BatchRecordRecordID, SourceID, BatchID)
SELECT /*+NO_QUERY_TRANSFORMATION*/ RecordID, SourceID, BatchID
FROM (
    SELECT RecordID, BatchID, 101 AS SourceID
    FROM BatchRecords
    WHERE BatchID = 150
    GROUP BY RecordID, BatchID
) BR