Re: Not a group by expression

On May 15, 2:45 pm, "astalavista" <nob..._at_nowhere.com> wrote:
> 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

A fairly easy one to answer -- this

SUBSTR (entry_datetime, :"SYS_B_00", :"SYS_B_01")

in your select list doesn't match this

SUBSTR (entry_datetime, :"SYS_B_11", :"SYS_B_12")

in your GROUP BY.

David Fitzjarrell Received on Thu May 15 2008 - 15:05:42 CDT