Query works on a version of oracle but not on other

Question

I have 2 versions of oracle, Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production and Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

And I have this 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;

It works on 11.2.0.1.0 but, on 11.2.0.3.0, I get this error:

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

What can I do to solve this?

Thx.

'01/01/2016' is not a date. Oracle may try to implicitly convert it to a date using the NLS_DATE_FORMAT session parameter as the format mask but if this mask does not match then the conversion will fail (and the parameter can be set by each user in their session so when it changes it is a pain to debug as it can work for one user and not others). It would be better to use TO_DATE( '01/01/2016', 'DD/MM/YYYY' ) or an ANSI date literal DATE '2016-01-01'. — MT0
The NLS_DATE_FORMAT is 'DD/MM/YYYY', Spanish format, anyway, I change dates but I keep getting the same error. — Darkerviti
You miss the point - '01/01/2016' is a string and not a date. Regardless of whether it works for you or not using strings for dates and relying on implicit conversion is bad practice as the user can change the NLS_DATE_FORMAT in their session and that will break all the queries where you rely on implicit conversion without the query changing. — MT0
Only thing that comes to mind is a possible mismatch between column names like cana and caña or cána. They may be used inconsistently between the code and the base table; depending on database settings, one may see them as the same but the other as different. If they are different between select and group by, that might throw this error. The error message says exactly where this error occurred, can you share that info as well? — mathguy

Michael Piankov Michael Piankov · Accepted Answer · 2017-01-26T11:14:07

I'm not sure why you get that error. Your query look like syntactically good. But extra complex. I try to rewrite it a bit. First of all I specify dates as dates. Then I exclude second subquery and compile it with first one. And in result I get next:

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

Query works on a version of oracle but not on other

1 Answers