Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Group By Error!!! Please help (10g)
Oracle Group By Error!!! Please help [message #551310] Tue, 17 April 2012 03:03 Go to next message
shiveclat
Messages: 19
Registered: August 2011
Location: pune
Junior Member
Hi All,
I have below query not sure why but I a getting ERROR
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
can some please help me with this.
Thanks

select LR.queue_name, LR.System, LR.Component, LR.Item, LR.Module
from TBL_PRGN_SCIM_LOOKUP LR
group by LR.queue_name
having count(distinct LR.Application_Name) > 1;
Re: Oracle Group By Error!!! Please help [message #551313 is a reply to message #551310] Tue, 17 April 2012 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59793
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have below query not sure why but I a getting ERROR


What is the value of "LR.System" when you group by "LR.queue_name" and there are several "LR.System"? (and same thing for the 3 other columns).

Regards
Michel
Re: Oracle Group By Error!!! Please help [message #551315 is a reply to message #551313] Tue, 17 April 2012 03:25 Go to previous messageGo to next message
shiveclat
Messages: 19
Registered: August 2011
Location: pune
Junior Member
The value of the LR.System is Char only and also same for the 3 other columns
e.g.
System Component Item Module
APPLICATION MBAPEX ABARAIF PERFORM
what I am looking for is Queue names for the distinct application names from this table
right now I have 214 distinct application names present in this table but if I select only two columns application names and queue name and order by these two then I am getting result as 298 so few application names are repeting that I do not want.
Thanks
Re: Oracle Group By Error!!! Please help [message #551317 is a reply to message #551315] Tue, 17 April 2012 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59793
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The value of the LR.System is Char only and also same for the 3 other columns


This is not their value, it is their data type.

For instance:
col1 col2
   1    1
   1    2
   1    3

If I group on "col1", what is the value of "col2"?
Or if you prefer, what should be the result of (invalid query):
select col1, col2 from t group by col1

Regards
Michel
Re: Oracle Group By Error!!! Please help [message #569184 is a reply to message #551317] Mon, 22 October 2012 07:57 Go to previous messageGo to next message
fgauna
Messages: 1
Registered: October 2012
Location: iq
Junior Member
I encountered the same problem and it does not seem to be consistent.
The fix I was given was to

SQL> alter session set"_complex_view_merging"=false;

That solved the problem for me. I have no explanation as to why or what
is going on but give that a try.

Fernando G.
Re: Oracle Group By Error!!! Please help [message #569185 is a reply to message #569184] Mon, 22 October 2012 08:05 Go to previous message
joy_division
Messages: 4546
Registered: February 2005
Location: East Coast USA
Senior Member
fgauna wrote on Mon, 22 October 2012 08:57
I encountered the same problem and it does not seem to be consistent.
The fix I was given was to

SQL> alter session set"_complex_view_merging"=false;

That solved the problem for me. I have no explanation as to why or what
is going on but give that a try.

Fernando G.


Either you are answering some other question or I do not understand you. What does this have to do about the OP not understanding how to properly use a GROUP BY statement?
There is no "fix" for not using a proper query.
Previous Topic: How to get correct minute between two hours?
Next Topic: Table Instance Chart Question
Goto Forum:
  


Current Time: Wed Nov 26 14:30:59 CST 2014

Total time taken to generate the page: 0.15801 seconds