Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00979: not a GROUP BY expression (oracle 18c )
ORA-00979: not a GROUP BY expression [message #682637] Wed, 04 November 2020 07:22 Go to next message
Haykel_bh
Messages: 5
Registered: November 2020
Junior Member

Hi,
A query is executed on oracle 11.2.1.0 bat generates an error ORA-00979: not a GROUP BY expressionon oracle 18c.

with c2
as
(SELECT  f2.module_id,I2.rec_unit_id, I2.origin, f2.object_type 
                                      from g_cashitem I2 INNER JOIN f_followup f2 on I2.item_id=f2.object_id
                                                          INNER JOIN g_rec_unit r2 ON (I2.rec_unit_id = r2.rec_unit_id)
)                                                          
SELECT /*+ NO_QUERY_TRANSFORMATION */XMLAGG (XMLELEMENT ("Items1",
                           xmlattributes ( rec_unit_id, COUNT ( *) AS total),
                           (SELECT XMLAGG (XMLELEMENT ("Items2",
                                                       xmlattributes ( c2.rec_unit_id,c2.object_type, COUNT ( *) AS total),
                                                       (SELECT XMLAGG ( XMLELEMENT ( "Items3", xmlattributes ( rec_unit_id,object_type,origin, COUNT ( *) AS total) 
                                                                    ))
                                                          FROM ( SELECT  f1.module_id,I3.rec_unit_id, I3.origin, f1.object_type 
                                                            from g_cashitem I3 INNER JOIN f_followup f1 on I3.item_id=f1.object_id
                                                          INNER JOIN g_rec_unit r ON (I3.rec_unit_id = r.rec_unit_id)
                                                          )c3
                                                        WHERE  c3.rec_unit_id = c1.rec_unit_id
                                                        AND c3.object_type=c2.object_type
                                                        GROUP BY rec_unit_id,object_type,origin)
                                                      )
                                          )
                              FROM c2                         
                           GROUP BY rec_unit_id,object_type
                           )
                          )
              )
  FROM g_cashitem c1
 WHERE page_id = 11927
GROUP BY rec_unit_id;
Re: ORA-00979: not a GROUP BY expression [message #682639 is a reply to message #682637] Wed, 04 November 2020 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In this case you have to provide a test case: CREATE TABLE and INSERT statements for some data so we can reproduce what you have in different versions to know if it is a bug in one of these versions.

You have to provide the complete version numbers also for your 18c version (query v$version and/or provide the db message when you connect with SQL*Plus).

Re: ORA-00979: not a GROUP BY expression [message #682650 is a reply to message #682639] Thu, 05 November 2020 02:37 Go to previous messageGo to next message
Haykel_bh
Messages: 5
Registered: November 2020
Junior Member
Hi,

select * from v$version;
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production "Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
Version 18.3.0.0.0" Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production 0

CREATE TABLE and INSERT statements for some data
CREATE TABLE G_REC_UNIT
(
  REC_UNIT_ID          NUMBER(38)               NOT NULL
);

CREATE TABLE G_CASHITEM
(
  ITEM_ID                 NUMBER(38)            NOT NULL,
  REC_UNIT_ID             NUMBER(38)            NOT NULL,
  PAGE_ID                 NUMBER(38),
  ORIGIN                  VARCHAR2(1 BYTE)      NOT NULL
);

CREATE TABLE F_FOLLOWUP
(
  MODULE_ID                 VARCHAR2(20 BYTE)   NOT NULL,
  OBJECT_TYPE               VARCHAR2(1 BYTE)    NOT NULL,
  OBJECT_ID                 INTEGER             NOT NULL
);

INSERT INTO G_REC_UNIT(REC_UNIT_ID) VALUES(1);

INSERT INTO G_CASHITEM(ITEM_ID,REC_UNIT_ID,PAGE_ID,ORIGIN) VALUES(10405,1,11927,'N');

INSERT INTO G_CASHITEM(ITEM_ID,REC_UNIT_ID,PAGE_ID,ORIGIN) VALUES(10399,1,11927,'N');

INSERT INTO G_CASHITEM(ITEM_ID,REC_UNIT_ID,PAGE_ID,ORIGIN) VALUES(10400,1,11927,'N');

INSERT INTO F_FOLLOWUP( MODULE_ID,OBJECT_TYPE,OBJECT_ID) VALUES('M1','I',10405);

INSERT INTO F_FOLLOWUP( MODULE_ID,OBJECT_TYPE,OBJECT_ID) VALUES('M1','I',10400);

INSERT INTO F_FOLLOWUP( MODULE_ID,OBJECT_TYPE,OBJECT_ID) VALUES('M1','I',10399);

Re: ORA-00979: not a GROUP BY expression [message #682651 is a reply to message #682650] Thu, 05 November 2020 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me in versions: 11.2.0.4.181016, 12.1.0.2.190716, 12.2.0.1.200714, 18.11.0.0.200714, 19.8.0.0.200714 but indeed not in 18.3.0.0.180717.
So we have to admit there is a bug in this version.
I'm not surprised as I have encountered a similar thing (although the opposite: something that works only in this version).

[Updated on: Thu, 05 November 2020 05:11]

Report message to a moderator

Re: ORA-00979: not a GROUP BY expression [message #682664 is a reply to message #682651] Fri, 06 November 2020 03:07 Go to previous message
Haykel_bh
Messages: 5
Registered: November 2020
Junior Member
thank you so much, it's a bug in this version
Previous Topic: How can I execute multiple SQL statements in one batch?
Next Topic: OLAP sql-cube operator
Goto Forum:
  


Current Time: Sun May 16 13:45:30 CDT 2021