Forum Stats

  • 3,715,501 Users
  • 2,242,777 Discussions
  • 7,845,371 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

ORA-00979: not a GROUP BY expression (oracle 18c )

user4841925
user4841925 Member Posts: 1 Red Ribbon

Hi,

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

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);

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

SELECT 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 (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) 

                  ) c2             

              GROUP BY rec_unit_id,object_type

              )

             )

       )

 FROM g_cashitem c1

 WHERE page_id = 11927

GROUP BY rec_unit_id

Answers

Sign In or Register to comment.