首页 文章

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

提问于
浏览 次
1

我有2个版本的oracle， Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 和 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

我有这个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:

我该怎么做才能解决这个问题？

谢谢 .

oracle oracle11g group-by

1 回答

  • 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
    回复于

相关问题