CODEWITHSUNDEEP
sqloracle-databasesymfonydoctrine-ormdql
Nico
Nico

Reputation: 1251

oracle ora-00979 symfony2 doctrine2

I'm building the following DQL:

SELECT partial operador.{id, pmn, neteo},                           
sum(outcollect.montoTotal) as montoOut,
outcollect.periodo as periodo,
outcollect.pagado
FROM RoamingOperadoresBundle:Operador operador
LEFT JOIN operador.operadorHub operadorHub 
LEFT JOIN operador.outcollect outcollect
WHERE operador.ishub = 0
AND operador.neteo = 1                          
AND operador.incluirReportes = 1
AND operador.pmn not in('CHLTM', 'CHLCM')
AND operador.id not in(SELECT op.id 
                       FROM RoamingOperadoresBundle:Operador op
                       LEFT JOIN op.operadorHub opHub
                       LEFT JOIN op.outcollect out
                       WHERE (opHub.desde <= out.periodo and (opHub.hasta >= out.periodo or opHub.hasta is null))
                       )
AND operador.activo = 1
GROUP BY operador.pmn, outcollect.periodo ORDER BY operador.pmn

Which transforms into this SQL:

SELECT o0_.id AS ID0, 
o0_.pmn AS PMN1, 
o0_.neteo AS NETEO2, 
sum(o1_.monto_total) AS SCLR3, 
o1_.periodo AS PERIODO4, 
o1_.pagado AS PAGADO5 
FROM operador o0_ 
LEFT JOIN operador_hub o2_ ON o0_.id = o2_.operador_id 
LEFT JOIN outcollect o1_ ON o0_.id = o1_.operador_id 
WHERE o0_.ishub = 0 
AND o0_.neteo = 1 
AND o0_.incluirReportes = 1 
AND o0_.pmn NOT IN ('CHLTM', 'CHLCM') 
AND o0_.id NOT IN (SELECT o3_.id FROM operador o3_ LEFT JOIN operador_hub o4_ ON o3_.id = o4_.operador_id LEFT JOIN outcollect o5_ ON o3_.id = o5_.operador_id WHERE (o4_.desde <= o5_.periodo AND (o4_.hasta >= o5_.periodo OR o4_.hasta IS NULL))) 
AND o0_.activo = 1 
GROUP BY o0_.pmn, o1_.periodo 
ORDER BY o0_.pmn ASC

Which gives the following error:

ORA-00979: no es una expresión GROUP BY

Any pointers on how to change the DQL to solve this problem?

I'm using doctrine2, symfony2 and OCI8

Upvotes: 0

Views: 129

Answers (1)

Rahul
Rahul

Reputation: 77876

Per Oracle documentation

ORA-00979 not a GROUP BY expression Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.

Action: Include in the GROUP BY clause all SELECT expressions that 
  are not group function arguments.

So you need to include all the expressions/column in select list to your group by. change the group by of your SQL query to below

group by o0_.pmn, o1_.periodo, o0_.neteo, o1_.pagado, o0_.id

GROUP BY of DQL to

GROUP BY operador.pmn, outcollect.periodo, operador.id, 
operador.neteo, outcollect.pagado

Upvotes: 1

Related Questions