| ORA-00979: not a GROUP BY expression [message #202764] |
Sun, 12 November 2006 00:12  |
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 #202802 is a reply to message #202764] |
Sun, 12 November 2006 21:52  |
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 #203038 is a reply to message #202764] |
Mon, 13 November 2006 12:05 |
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 |
skooman
Messages: 913
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
