CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Tom Halladay
Tom Halladay

Reputation: 5761

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

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.

Upvotes: 15

Views: 4964

Answers (4)

abrittaf
abrittaf

Reputation: 537

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

Upvotes: 11

Diego Gonzalez
Diego Gonzalez

Reputation: 1

The problem is solved by automatically changing the value of a parameter (optimizer_features_enable). This value determines the optimizer version of the base, with 11 should not give that problem.

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Wonder if it's an order of execution problem... does it work if you use a CTE? CTE must first materialize thus resolve the group by...

  Insert INTO MasterRecords (BatchRecordRecordID, SourceID, BatchID)
   WITH BR AS (
    SELECT RecordID, 101 AS SourceID, 150 AS BatchID
    FROM BatchRecords
    GROUP BY RecordID, 101,150)
  Select RecordID, SourceID, BatchID FROM BR

or... why the group by and where clause in the first place doesn't seem to be doing anything since recordID isn't an aggregate and isn't part of the group by...

Insert into masterRecords (batchrecordRecordID, SourceID, BatchID) SELECT recordID, 101, 150 from batchRecords

Upvotes: 0

MrSimpleMind
MrSimpleMind

Reputation: 8597

Am I thinking wrong, but is not the sql below equal what you want to achieve?

INSERT INTO MasterRecords(BatchRecordRecordID, SourceID, BatchID)
SELECT DISTINCT RecordID, 101, 150
FROM BatchRecords
WHERE BatchID = 150
;

Upvotes: 3

Related Questions