Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00979: not a GROUP BY expression
ORA-00979: not a GROUP BY expression [message #202764] Sun, 12 November 2006 00:12 Go to next message
santoshmail
Messages: 6
Registered: January 2006
Location: Gurgaon
Junior Member
1 select cal.curr_mth_no mth_no, cal.curr_week_no week_no, max(cal.curr_dt ) n_day
2 from calendar cal
3 where cal.plnt_cd = '303000'
4 and cal.info_type = 'ABS'
5 and cal.curr_mth_no = '200612'
6 and (cal.curr_mth_no, cal.curr_week_no, cal.curr_dt) in
7 (select w.mth_no, w.week_no, nvl(max(c.curr_dt), w.curr_week_strt_dt)
8 from calendar c, week_info w
9 where w.plnt_cd = '303000'
10 and w.info_type = 'ABS'
11 and w.week_no = cal.curr_week_no
12 and w.plnt_cd = c.plnt_cd (+)
13 and c.holiday_flg(+) = '0'
14 and rownum <= (5 - 1)
15 group by w.mth_no, w.week_no)
16 group by cal.curr_mth_no, cal.curr_week_no;


Following query is working fine.
In inner query is using one group by function max (on line 7) and the the group by clause of this
inner query is containing only two fields w.mth_no and w.week_no (line 15).
In fact is should contain three fields w.mth_no , w.week_no and w.curr_week_strt_dt.

But this is working fine.
When i am commenting line no 14, I am getting the error:
ORA-00979: not a GROUP BY expression

Can anyone explane why is like this?

Re: ORA-00979: not a GROUP BY expression [message #202770 is a reply to message #202764] Sun, 12 November 2006 04:34 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Can you pl. post the structure of both tables involved in the query?
Re: ORA-00979: not a GROUP BY expression [message #202771 is a reply to message #202764] Sun, 12 November 2006 04:36 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please use code tags for more visibility.

By
Vamsi.
Re: ORA-00979: not a GROUP BY expression [message #202802 is a reply to message #202764] Sun, 12 November 2006 21:52 Go to previous messageGo to next message
santoshmail
Messages: 6
Registered: January 2006
Location: Gurgaon
Junior Member
Table calendar structure
------------------------
COLUMN_NAME NULLABLE DATA_TYPE
----------- -------- ---------
PLNT_CD N CHAR
CURR_DT N DATE
INFO_TYPE N CHAR
TERM_CD Y CHAR
MON_ACT_WORK_DAYS Y NUMBER
WEEK_1_WORK_DAYS Y NUMBER
WEEK_2_WORK_DAYS Y NUMBER
WEEK_3_WORK_DAYS Y NUMBER
WEEK_4_WORK_DAYS Y NUMBER
WEEK_5_WORK_DAYS Y NUMBER
WEEK_6_WORK_DAYS Y NUMBER
ACT_WEEK_NO_1 Y CHAR
ACT_WEEK_NO_2 Y CHAR
ACT_WEEK_NO_3 Y CHAR
ACT_WEEK_NO_4 Y CHAR
ACT_WEEK_NO_5 Y CHAR
ACT_WEEK_NO_6 Y CHAR
CURR_YR_NO N CHAR
CURR_MTH_NO N CHAR
CURR_WEEK_NO N CHAR
CURR_WEEK_STRT_DT Y DATE
CURR_WEEK_END_DT Y DATE
ACT_WORK_DAY_CURR Y NUMBER
HOLIDAY_FLG N NUMBER
DAY_OF_WEEK N CHAR


Table week_info structure
-------------------------
COLUMN_NAME NULLABLE DATA_TYPE
----------- -------- ---------
PLNT_CD N CHAR
INFO_TYPE N CHAR
MTH_NO N CHAR
MTH_NAME Y VARCHAR2
WEEK_NO N CHAR
NO_OF_WORK_DAYS Y NUMBER
TOT_NO_OF_WORK_DAYS Y NUMBER
X_WEEK_IND N CHAR
CURR_WEEK_STRT_DT Y DATE
Re: ORA-00979: not a GROUP BY expression [message #202803 is a reply to message #202771] Sun, 12 November 2006 22:04 Go to previous messageGo to next message
santoshmail
Messages: 6
Registered: January 2006
Location: Gurgaon
Junior Member
What is code tag??

This is sample Sql query. And the problem is of "Group By Clause"
behavior.
Re: ORA-00979: not a GROUP BY expression [message #202833 is a reply to message #202803] Mon, 13 November 2006 00:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Sorry! It seems you have confused. I was talking about formatting your post...

How to format your posts

By
Vamsi
Re: ORA-00979: not a GROUP BY expression [message #203038 is a reply to message #202764] Mon, 13 November 2006 12:05 Go to previous messageGo to next message
artmast
Messages: 11
Registered: October 2006
Junior Member
From the Oracle documentation in oratip http://oratip.com/ORA-00979.html I found the following:
Cause
The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.

Action
Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

Good luck
Art.
Re: ORA-00979: not a GROUP BY expression [message #203075 is a reply to message #203038] Mon, 13 November 2006 15:46 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
But at a glance, all the select items are indeed in the group by, so that can not be the cause.

Having a closer look, could it be that this line:
11 and w.week_no = cal.curr_week_no


is causing the error? The where clause does seem a bit strange, since it is in the subquery, the calendar table is there and why correlate it to the main query? And that might be the confusing part for the group by. I'm not sure (can't test it), but I would try changing it to:
11 and w.week_no = c.curr_week_no
Re: ORA-00979: not a GROUP BY expression [message #203129 is a reply to message #203075] Tue, 14 November 2006 00:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sabine wrote
But at a glance, all the select items are indeed in the group by, so that can not be the cause.


But, at second glance...
 7 (select w.mth_no, w.week_no, nvl(max(c.curr_dt), w.curr_week_strt_dt)
...
15 group by w.mth_no, w.week_no)

w.curr_week_strt_dt is not in the group by.

Wink

Re: ORA-00979: not a GROUP BY expression [message #203164 is a reply to message #203129] Tue, 14 November 2006 03:09 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Verrek! Or, in English, oops Wink
Previous Topic: Rollback
Next Topic: Using UTL_MAIL
Goto Forum:
  


Current Time: Sun Dec 11 05:55:20 CST 2016

Total time taken to generate the page: 0.08138 seconds