SELECT with 'Group by' clause fails with ORA-979 (Doc ID 1489457.1)

Last updated on SEPTEMBER 29, 2021

Applies to:

Symptoms

When attempting to run a SELECT with a 'group by' clause over a user-defined domain index like below:

CREATE OR REPLACE OPERATOR TestFastOp
BINDING
(RAW, VARCHAR2, VARCHAR2, RAW, DATE) RETURN DATE
WITH INDEX CONTEXT, SCAN CONTEXT TEST_IDX_IMPL_TYPE
USING Test$FastDate,
(RAW, VARCHAR2, VARCHAR2, VARCHAR2, RAW, DATE) RETURN DATE
WITH INDEX CONTEXT, SCAN CONTEXT TEST_IDX_IMPL_TYPE
USING Test$FastDateByName;

CREATE OR REPLACE INDEXTYPE TEST_IDX_TYPE
FOR
TestFastOp(RAW, VARCHAR2, VARCHAR2, RAW, DATE),
TestFastOp(RAW, VARCHAR2, VARCHAR2, VARCHAR2, RAW, DATE)
USING TEST_IDX_IMPL_TYPE;

CREATE VIEW "SAMPLE"."DATETEMP_IDV" AS SELECT SAMPLE.TestFastOp("COL1_NEW", 'SAMPLE', 'DATETEMP_NEW', 'COL1_NEW','1E1DEED7E50F93E19D0D18BD4790703B', TO_DATE('21-OCT-2002', 'DD-MON-YYYY'))"COL1", "TEST_ROW_ID" FROM "SAMPLE"."DATETEMP_NEW";

CREATE VIEW "SAMPLE"."DATETEMP" AS SELECT "COL1" FROM "SAMPLE"."DATETEMP_IDV";

SELECT COL1 FROM DATETEMP GROUP BY COL1;

The following error occurs:

ORA-979: not a GROUP BY expression

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.