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

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms