Re: Not a group by expression

On May 15, 4:04 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> astalavista schrieb:
> > Hi,
>
> > When I try an explain plan on the query below
> > I received ORA-00979: not a GROUP BY expression
>
> > Do you know why ?
>
> > Thanks
> > Oracle 9.2.0.6
> > SELECT SUBSTR (entry_datetime, :"SYS_B_00", :"SYS_B_01"), entry_id, COUNT
> > (*)
>
> > FROM (SELECT entry_datetime, entry_id FROM cl318886012.inf_errorstatus
>
> > WHERE entry_datetime >= TO_DATE (:"SYS_B_02", :"SYS_B_03")
>
> > AND entry_datetime < TO_DATE (:"SYS_B_04", :"SYS_B_05")
>
> > AND ctv_dstatus = :"SYS_B_06"
>
> > AND entry_id LIKE :"SYS_B_07"
>
> > AND ctv_error_id NOT IN ( SELECT ctv_error_id
>
> > FROM cl318886012.inf_errorstatus_audit
>
> > WHERE ctv_dstatus = :"SYS_B_08"
>
> > AND entry_datetime >= TO_DATE (:"SYS_B_09",:"SYS_B_10")))
>
> > GROUP BY SUBSTR (entry_datetime, :"SYS_B_11", :"SYS_B_12"), entry_id
>
> Seems to fit into Bug 3668572 (despite the fact, Metalink claims it to
> be fixed in 9.2.0.6). You could try workarounds mentioned in the Note
> 3668572.8.
>
> Best regards
>
> Maxim

I remember hitting a similar bug on 10.2.0.2 when cursor sharing was set to FORCE - I think that I encountered it when attempting to use GROUP BY on a date column formatted to include just the month and year {ex: TO_CHAR(DATE_COL,'YYYY-MM') }. In my case, I had to rewrite the query to generate the TO_CHAR version of the column inside the inline view, and then use GROUP BY on that generated column. It appears that the same solution of generating a column {ex: SUBSTR (entry_datetime, 1, 5) MY_COL for example} will work for the OP.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu May 15 2008 - 15:39:00 CDT