ORA-00979: not a GROUP BY expression Error

Question

I am facing a strange issue, my OBIEE 11g report is having multiple UNions and it works fine with several other DEV and PROD Database instances, but gives an ORA-00979 error with only 1 test database instance.

Anyone faced the same problem or any thoughts on the root cause ?

Gianni Ceresa · Answer

An ORA-xxxxx error is not an OBIEE error but an Oracle database error.

So if you at least want some hints you will need to be a bit more explicit and post some infos on the relative DB and at least look what kind of physical query OBIEE generated and compare it with the one generated on the other environments to identify if the physical query is the same (=> which point you to explore your DB settings and look for a difference there) or if the generated query is different (=> which can point you to a mismatching config in the RPD at the physical DB level).

ArijitC · Answer

Check the version difference of the odd one with other databases. It probably generating different explain plan. Capture the explain plan in both databases. That will give you and insight.

Thanks

Arijit