OraFAQ Forum: SQL & PL/SQL » Oracle Group By Error!!! Please help

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Group By Error!!! Please help (10g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Oracle Group By Error!!! Please help [message #551310]

Tue, 17 April 2012 03:03

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;

Report message to a moderator

Re: Oracle Group By Error!!! Please help [message #551313 is a reply to message #551310]

Tue, 17 April 2012 03:13

Michel Cadot
Messages: 54236
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

Report message to a moderator

Re: Oracle Group By Error!!! Please help [message #551315 is a reply to message #551313]

Tue, 17 April 2012 03:25

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

Report message to a moderator

Re: Oracle Group By Error!!! Please help [message #551317 is a reply to message #551315]

Tue, 17 April 2012 03:42

Michel Cadot
Messages: 54236
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:

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

Report message to a moderator

Re: Oracle Group By Error!!! Please help [message #569184 is a reply to message #551317]

Mon, 22 October 2012 07:57

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.

Report message to a moderator

Re: Oracle Group By Error!!! Please help [message #569185 is a reply to message #569184]

Mon, 22 October 2012 08:05

joy_division
Messages: 4267
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.

Report message to a moderator

Previous Topic:	How to get correct minute between two hours?
Next Topic:	Table Instance Chart Question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 22:33:58 CDT 2013

Total time taken to generate the page: 0.21035 seconds