Error en agrupado de datos
Publicado por Mariano José (6 intervenciones) el 07/03/2018 18:57:39
Muy buen día a todos, soy nuevo usuario en la página y quiero ver quien me puede ayudar a resolver un problema que tengo con la consulta que abajo les detallo, en esta se remarca el siguiente error "ORA-00979: Not a group by expression".
Les agradesco por su tiempo y ayuda.
*****Consulta*****
select
he.instancia, de.cd_upnivel1 as finca, de.cd_upnivel3 as lote, u1.de_upnivel1 as desc_finca,
de.cd_operacao, (select de_operacao from pimscs.operacoes where cd_operacao=de.cd_operacao) as desc_opr,
a.de_unid_adm as zona, s.cd_mod_adm as supervisor,
sum(de.qt_prod_exec) as ha_per,
(select sum(a2.qt_prod_exec)
from pimscs.apt_mec_he a1 left outer join pimscs.apt_mec_de a2
on a1.instancia=a2.instancia and a1.no_boletim=a2.no_boletim and a1.fg_tp_apto=a2.fg_tp_apto
where a2.instancia=de.instancia
and a2.cd_upnivel1=de.cd_upnivel1 and a2.cd_upnivel3=de.cd_upnivel3 and a2.cd_operacao=de.cd_operacao
and a1.dt_operacao between to_date(''''01/11/2017'''',''''DD/MM/YYYY'''') and to_date(''''02/03/2018'''',''''DD/MM/YYYY'''')
and a2.cd_operacao in(211,212,221,222,223,226,227,231,232,239,240,241,242,243,615,831,832,833,921,922,945,947,981,982,1320)
) as ha_acum,
sum(u3.qt_area_prod) as ha_lote
from pimscs.apt_mec_he he left outer join pimscs.apt_mec_de de
on he.instancia=de.instancia and he.no_boletim=de.no_boletim and he.fg_tp_apto=de.fg_tp_apto
left outer join pimscs.upnivel1 u1
on de.cd_upnivel1=u1.cd_upnivel1
left outer join pimscs.upnivel3 u3
on de.cd_upnivel1=u3.cd_upnivel1
and de.cd_upnivel3=u3.cd_upnivel3
and u3.cd_safra in(select max(cd_safra) from pimscs.upnivel3
where cd_upnivel1=u3.cd_upnivel1 and cd_upnivel3=u3.cd_upnivel3)
left outer join pimscs.moduloadm s
on u3.cd_mod_adm=s.cd_mod_adm
left outer join pimscs.uniadm a
on s.cd_unid_adm=a.cd_unid_adm
where he.instancia=''''NI01''''
and he.dt_operacao between to_date(''''17/02/2018'''',''''DD/MM/YYYY'''') and to_date(''''02/03/2018'''',''''DD/MM/YYYY'''')
and cd_operacao in(211,212,221,222,223,226,227,231,232,239,240,241,242,243,615,831,832,833,921,922,945,947,981,982,1320)
group by he.instancia, de.cd_upnivel1, de.cd_upnivel3, u1.de_upnivel1, de.cd_operacao, a.de_unid_adm, s.cd_mod_adm
Les agradesco por su tiempo y ayuda.
*****Consulta*****
select
he.instancia, de.cd_upnivel1 as finca, de.cd_upnivel3 as lote, u1.de_upnivel1 as desc_finca,
de.cd_operacao, (select de_operacao from pimscs.operacoes where cd_operacao=de.cd_operacao) as desc_opr,
a.de_unid_adm as zona, s.cd_mod_adm as supervisor,
sum(de.qt_prod_exec) as ha_per,
(select sum(a2.qt_prod_exec)
from pimscs.apt_mec_he a1 left outer join pimscs.apt_mec_de a2
on a1.instancia=a2.instancia and a1.no_boletim=a2.no_boletim and a1.fg_tp_apto=a2.fg_tp_apto
where a2.instancia=de.instancia
and a2.cd_upnivel1=de.cd_upnivel1 and a2.cd_upnivel3=de.cd_upnivel3 and a2.cd_operacao=de.cd_operacao
and a1.dt_operacao between to_date(''''01/11/2017'''',''''DD/MM/YYYY'''') and to_date(''''02/03/2018'''',''''DD/MM/YYYY'''')
and a2.cd_operacao in(211,212,221,222,223,226,227,231,232,239,240,241,242,243,615,831,832,833,921,922,945,947,981,982,1320)
) as ha_acum,
sum(u3.qt_area_prod) as ha_lote
from pimscs.apt_mec_he he left outer join pimscs.apt_mec_de de
on he.instancia=de.instancia and he.no_boletim=de.no_boletim and he.fg_tp_apto=de.fg_tp_apto
left outer join pimscs.upnivel1 u1
on de.cd_upnivel1=u1.cd_upnivel1
left outer join pimscs.upnivel3 u3
on de.cd_upnivel1=u3.cd_upnivel1
and de.cd_upnivel3=u3.cd_upnivel3
and u3.cd_safra in(select max(cd_safra) from pimscs.upnivel3
where cd_upnivel1=u3.cd_upnivel1 and cd_upnivel3=u3.cd_upnivel3)
left outer join pimscs.moduloadm s
on u3.cd_mod_adm=s.cd_mod_adm
left outer join pimscs.uniadm a
on s.cd_unid_adm=a.cd_unid_adm
where he.instancia=''''NI01''''
and he.dt_operacao between to_date(''''17/02/2018'''',''''DD/MM/YYYY'''') and to_date(''''02/03/2018'''',''''DD/MM/YYYY'''')
and cd_operacao in(211,212,221,222,223,226,227,231,232,239,240,241,242,243,615,831,832,833,921,922,945,947,981,982,1320)
group by he.instancia, de.cd_upnivel1, de.cd_upnivel3, u1.de_upnivel1, de.cd_operacao, a.de_unid_adm, s.cd_mod_adm
Valora esta pregunta
0