ORA-00979: not a group by expression
Bonjour à tous,
une requête est exécutée sous oracle 11.2.0.1.0 mais génère une erreur ORA-00979:not a group by expression sous oracle 18c.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| 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 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; |
