查询适用于oracle的一个版本，但不适用于其他版本

Question

我有两个版本的oracle，oracle Database 11g Enterprise Edition 11.2.0.1.0版本-生产版和oracle Database 11.g Enterprise Edition版本11.2-0.3.0版本-64位生产版

我有这个sql：

select an.idai, t1.fecha, 53, t1.val_d60, 0 
from 
      ( select ides, cana, ctec, trunc(fecha_d60, 'MM') as fecha, val_d60, 
               row_number() over (partition by ides,cana,ctec, trunc(fecha_d60, 'MM')
                                  order by val_d60 asc) as orden 
        from azul_estdata60 
        where idflagv = 11 
        and ides < 25
        and fecha_d60 >= '01/01/2016' and fecha_d60 <= '31/12/2016'
        and cana = 8
        order by ides, cana, ctec, val_d60 desc
      ) t1, 
      ( select ides, cana, ctec, trunc(fecha_d60, 'MM') as fecha, 
        round(count(*)*.5,0) as percentil 
        from azul_estdata60 
        where idflagv in (11,12,13) 
        and ides < 25
        and fecha_d60 >= '01/01/2016' and fecha_d60 <= '31/12/2016'
        and cana = 8
        group by ides, cana, ctec, trunc(fecha_d60, 'MM')
      ) t2 
      inner join azul_analogin an
      on an.cana = t2.cana 
         and an.ctec = t2.ctec and an.ides = t2.ides 
where t1.ides = t2.ides 
and t1.cana = t2.cana 
and t1.ctec = t2.ctec 
and t1.fecha = t2.fecha 
and orden = percentil; 

它适用于11.2.0.1.0，但在11.2.0.3.0上，我收到此错误：

ORA-00979: no es una expresión GROUP BY
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:

我能做些什么来解决这个问题？

感谢。

Answer 1

我不知道你为什么会有这个错误。您查询在语法上看起来不错。但是非常复杂。我试着重写一点。首先，我指定日期为日期。然后我排除第二个子查询，用第一个子查询编译它。结果我得到下一个:

with azul_estdata60(cana, ctec, ides, fecha_d60,val_d60,idflagv ) as  (
select 8,123, 1, date'2016-01-01',200, 11 from dual union all
select 8,123, 1, date'2016-01-03',2000, 11 from dual union all
select 8,123, 1, date'2016-01-05',2000, 11 from dual union all
select 8,123, 1, date'2016-01-06',20000, 11 from dual union all
select 8,123, 1, date'2016-01-10',200000, 11 from dual union all
select 8,123, 2, date'2016-02-01',201, 12 from dual union all
select 8,123, 3, date'2016-03-01',203, 13 from dual union all
select 8,123, 4, date'2016-04-01',205, 14 from dual union all
select 8,123, 5, date'2016-05-01',219, 13 from dual union all
select 8,123, 6, date'2017-01-01',260, 11 from dual )
, azul_analogin (cana, ctec, ides, IDAI) as (
select 8,123, 1,991 from dual 
)
select an.idai, t2.fecha, 53, t2.val_d60, 0 , percentil, orden 
from 
      ( select ides, cana, ctec, trunc(fecha_d60, 'MM') as fecha, val_d60, 
               idflagv,
               round( (count(*) over (partition by ides, cana, ctec, trunc(fecha_d60, 'MM')))*0.5,0)  as percentil,
               row_number() over   (partition by idflagv, ides,cana,ctec, trunc(fecha_d60, 'MM')
                                        order by val_d60 asc) as orden  
        from azul_estdata60 
        where idflagv in (11,12,13) 
        and ides < 25
        and fecha_d60 >= date'2016-01-01' and fecha_d60 < date'2017-01-01'
        and cana = 8
      ) t2 
      inner join azul_analogin an
      on an.cana = t2.cana 
         and an.ctec = t2.ctec and an.ides = t2.ides 
where 1 = 1
and orden = percentil
and idflagv = 11