Re: A question about GROUP BY (ORA-00979: not a GROUP BY expression)

Lan Huang wrote:
> Hi,
> I'm trying the same query in Oracle 9i and Oracle 817 and receive
> different results. Can you help to explain the reason? Thanks!
>
> The query is
> select cast(deptno as number(10)), min(salary)
> from table1
> group by cast(deptno as number(10));
>
> The types of the columns are
> DEPTNO NUMBER(3)
> SALARY NUMBER(10)
>
> In Oracle 9i, I received the following error
> ERROR at line 1:
> ORA-00979: not a GROUP BY expression
>
> In Oracle 817, correct result is returned.
>
> Is there any new restrictions introduced in Oracle 9i? What is the
> reason Oracle 9i raises the error?
>
> Thanks,
> Lan

No but there may be something wrong with your typing:

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Dec 3 13:11:50 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> CREATE TABLE table1 (

   2 deptno number(3),
   3 salary number(10));

Table created.

SQL> SELECT CAST(deptno AS NUMBER(10)), MIN(salary)

   2 FROM table1
   3 GROUP BY CAST(deptno AS NUMBER(10));

no rows selected

SQL>

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)