ORA-12018: following error encountered during code generation for ..ORA-00979: not a GROUP BY expression

 ORA-12018 ORA-00979 ORA-06512

When we create Materialized report view it is successfully created and reports are coming properly.

But when we do complete refresh using below mentioned command it is showing an error

SQL> exec dbms_mview.refresh('mv_rkcapitaldashboard','C') ;

begin dbms_mview.refresh('mv_rkcapitaldashboard','C'); end;

ORA-12018: following error encountered during code generation for "LDBO"."MV_RKCAPITALDASHBOARD"

ORA-00979: not a GROUP BY expression

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994

 

Bug

Oracle Version 11.2.0.4

Solution

alter session set "_complex_view_merging"=false;

or add following in query

/*+ opt_param('_complex_view_merging','false') */

or try following in SQL

/*+ NO_QUERY_TRANSFORMATION */ 

exec dbms_mview.refresh('mv_rkcapitaldashboard','C') ;