Re: ORA-00979: not a GROUP BY expression

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

--
Photo gallery: http://www.pbase.com/john_russell/