Re: ORA-00979: not a GROUP BY expression

Thanks.

But this query is generated dynamically. So it's pretty hard for us to change rge app code.

I just don't understand why 2 same column aliases can cause the problem. Any help?

Regards,

John Russell <netnews5_at_johnrussell.mailshell.com> wrote in message news:<bpc28vga6h5f3546g389gcffhmdr6tkcgo_at_4ax.com>...
> On 25 Mar 2003 15:07:36 -0800, ibm_97_at_yahoo.com (JZ) wrote:
>
> >Oracle 9.2 on Linux:
> >
> >Query is:
> >
> >SELECT
> > DISTINCT
> > COUNT(SS_CNT_0) AS SG_1_SG
> > ,SS_VAL_1 AS SG_2_SG
> > ,SS_VAL_2 AS SG_3_SG
> > ,SS_VAL_3 AS SG_4_SG
> > ,'DEFAULT' AS SG_5_SG
> > , MAX(SS_VAL_4) AS SG_6_SG
> > ,'DEFAULT' AS SG_7_SG
> > ,'DEFAULT' AS SG_8_SG
> > ,'-1.0' AS SG_9_SG
> > ,'DEFAULT' AS SG_10_SG
> >FROM
> > ( SELECT
> > DISTINCT
> > h.EVENTID AS SS_CNT_0
> > ,TO_CHAR(h.APPTIMESTAMP,'HH24') AS SS_VAL_1
> > ,TO_CHAR(h.APPTIMESTAMP,'HH24') AS SS_VAL_2
> > ,n.DESCRIPTION AS SS_VAL_3
> > ,MAX(h.NFSEVERITY) AS SS_VAL_4
> >FROM
> > HIGHSEVERITYEVENTS h
> > , NFALARMS n
> >WHERE
> > h.NFALARMID = n.NFALARMID(+)
> > AND h.APPTIMESTAMP >= TO_DATE('2003-03-24 00:00:00',
> >'YYYY-MM-DD HH24:MI:SS')
> > AND h.APPTIMESTAMP < TO_DATE('2003-03-24 23:00:00',
> >'YYYY-MM-DD HH24:MI:SS')
> >GROUP BY
> > h.EVENTID
> > , TO_CHAR(h.APPTIMESTAMP,'HH24')
> > , TO_CHAR(h.APPTIMESTAMP,'HH24')
> > , n.DESCRIPTION
> > )
> >GROUP BY
> > SS_VAL_1
> > , SS_VAL_2
> > , SS_VAL_3
> >;
> >
> >The error is :
> >TO_CHAR(h.APPTIMESTAMP,'HH24') AS SS_VAL_2
> > *
> >ERROR at line 18:
> >ORA-00979: not a GROUP BY expression
> >
> >Any indeas? Thanks a lot!
>
> You're selecting the same expression twice and then including it twice
> in the GROUP BY. Why not select it once in the inner query, naming it
> once in the GROUP BY, and then select the column alias twice in the
> outer query? I find GROUP BY isn't very tolerant of column aliases,
> so I'd presume that the database can't tell which TO_CHAR... is
> supposed to map to which column alias from earlier in the query.
>
> John
Received on Wed Mar 26 2003 - 10:22:02 CST