Just as mentioned in the error you need to GROUP BY other columns in the
SELECT clause or apply GROUP functions (MIN MAX etc) to the selected
columns.


create or replace view emp_dept_view (emp_name,department,department_id)

as select e.name, d.dept_name, d.dept_id

from employee e, department d

group by d.dept_name, d.dept_name, d.dept_id ;

I am also assuming you have a WHERE clause that joins employee table to
department by dept_id.

Sumith

-----Original Message-----
From: Hussein Korkomaz
[mailto:oracledba-ezmlmshield-x89262491.[Email address protected]
Sent: Saturday, March 29, 2008 7:57 AM
To: LazyDBA Discussion
Subject: Error ORA-00979

Dear all



I am trying to create view with this command but it is give me error



create or replace view emp_dept_view (emp_name,department,department_id)

as select e.name, d.dept_name, d.dept_id

from employee e, department d

group by d.dept_name ;



Error report:

SQL Error: ORA-00979: not a GROUP BY expression



Same if I use group by d.dept_name ;



Any advice?????



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




Oracle LazyDBA home page