BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

[Deski (XI)] ORA-00979 not GROUP BY expression


 
Search this topic... | Search Desktop Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Author Message
Wittk
Forum Member
Forum Member



Joined: 23 Jan 2009

Posts: 5



PostPosted: Fri Jan 23, 2009 7:09 am 
Post subject: ORA-00979 not GROUP BY expression

Please, who can help me?

I use free SQL in my report to get one role for each project. I have one table AV_ACTIVITY with projects (PROJ), each project has one or more versions (VER), each version has one ore more activities (ACT with DS), with a startdate (ASD actual start or ES early start). What I want is one role per project with the activity with the date in the past but nearest to today.
My SQL gives an error and I don't now what's wrong banghead.gif

This is my SQL

SELECT AV_ACTIVITY.PROJ, AV_ACTIVITY.VER, AV_ACTIVITY.ACT , AV_ACTIVITY.DS , nvl(AV_ACTIVITY.ASD,AV_ACTIVITY.ES) as startmp , maxmp
FROM
AV_ACTIVITY,
( SELECT AV_ACTIVITY.PROJ, AV_ACTIVITY.VER,
Max(nvl(AV_ACTIVITY.ASD,AV_ACTIVITY.ES) ) OVER
(PARTITION BY AV_ACTIVITY.PROJ, AV_ACTIVITY.VER )
as maxmp
FROM
AV_ACTIVITY
WHERE
nvl(AV_ACTIVITY.ASD,AV_ACTIVITY.ES) <=
sysdate and AV_ACTIVITY.VER = 0
GROUP BY
AV_ACTIVITY.PROJ, AV_ACTIVITY.VER) )
max_mijlpaal
WHERE
nvl(AV_ACTIVITY.ASD,AV_ACTIVITY.ES) = maxmp
GROUP BY
AV_ACTIVITY.PROJ, AV_ACTIVITY.VER, AV_ACTIVITY.ACT ,AV_ACTIVITY.DS
Back to top
pablolee
Principal Member
Principal Member



Joined: 29 Jul 2008

Posts: 406


flag
PostPosted: Fri Jan 23, 2009 7:50 am 
Post subject: Re: ORA-00979 not GROUP BY expression

Various issues.
1. When you post code, format it, and place it between code tags to preserve the formatting to help others read your code:
Code:

SELECT    AV_ACTIVITY.PROJ
   , AV_ACTIVITY.VER
   , AV_ACTIVITY.ACT
   , AV_ACTIVITY.DS
   , nvl(AV_ACTIVITY.ASD,AV_ACTIVITY.ES) as startmp
   , maxmp
FROM
   AV_ACTIVITY,
   ( SELECT AV_ACTIVITY.PROJ
      , AV_ACTIVITY.VER
      , Max(nvl(AV_ACTIVITY.ASD,AV_ACTIVITY.ES) )
                                     OVER (PARTITION BY AV_ACTIVITY.PROJ
                                      , AV_ACTIVITY.VER ) as maxmp
   FROM AV_ACTIVITY
   WHERE nvl(AV_ACTIVITY.ASD,AV_ACTIVITY.ES) <= sysdate
   and AV_ACTIVITY.VER = 0
   GROUP BY AV_ACTIVITY.PROJ
      , AV_ACTIVITY.VER)
) max_mijlpaal
WHERE nvl(AV_ACTIVITY.ASD,AV_ACTIVITY.ES) = maxmp
GROUP BY  AV_ACTIVITY.PROJ
   , AV_ACTIVITY.VER
   , AV_ACTIVITY.ACT
   , AV_ACTIVITY.DS

2. Extra close bracket at the end of your sub query.
3. Unnecessary GROUP BY Clause.
Back to top
Wittk
Forum Member
Forum Member



Joined: 23 Jan 2009

Posts: 5



PostPosted: Fri Jan 23, 2009 8:56 am 
Post subject: Re: ORA-00979 not GROUP BY expression

Thank you for the quick respons.

But I don't think the bracket is the solution. Then I get an error ORA-00933 SQL-opdracht is niet juist beeindigd.

And witch GROUP By -clause is unnecessary?

Please take a look again and help me?!? seeya.gif
Back to top
pablolee
Principal Member
Principal Member



Joined: 29 Jul 2008

Posts: 406


flag
PostPosted: Fri Jan 23, 2009 9:24 am 
Post subject: Re: ORA-00979 not GROUP BY expression

I didn't say that the bracket was the solution, I said that the bracket was one of the problems (big difference).
As for the GROUP BY Clause, you only have two. How about you think about the code that you have written, think about what you know about the group by clause and when it is actually required, and then decide which (if any) of those group by clauses you actually need
Back to top
Wittk
Forum Member
Forum Member



Joined: 23 Jan 2009

Posts: 5



PostPosted: Fri Jan 23, 2009 11:01 am 
Post subject: Re: ORA-00979 not GROUP BY expression

The Group By wasn't required. You have helped me a lot.

Thanks for now and have a nice weekend!
Back to top
pablolee
Principal Member
Principal Member



Joined: 29 Jul 2008

Posts: 406


flag
PostPosted: Mon Jan 26, 2009 3:52 am 
Post subject: Re: ORA-00979 not GROUP BY expression

Glad you got it sorted icon_biggrin.gif and I had a great weekend, thanks.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0289 seconds using 17 queries. (SQL 0.0029 Parse 0.0009 Other 0.0251)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo