0

Can anyone see anything wrong with this statement?

CREATE OR REPLACE VIEW V_DEPT_AMOUNT AS       
	      SELECT DEPTNO AS DNO, DNAME AS DNAME, MAX(AMOUNT) AS  MAX_AMOUNT, 
              AVG(AMOUNT) AS AVG_AMOUNT, MIN(AMOUNT) AS MIN_AMOUNT, 
              SUM(AMOUNT) AS  TOTAL_AMOUNT FROM DEPT D, EMP E, PURCHASE P
              WHERE D.DEPTNO = E.DEPTNO
	      GROUP BY DNAME, DEPTNO;

It is meant to list names and numbers of all company departments together with the maximum, minimum, average and total amount contributed by each department.


The error I am getting is that "column ambiguously defined"

3
Contributors
4
Replies
5
Views
5 Years
Discussion Span
Last Post by hfx642
0

You should define alias to column name also. Which column belongs to which table. It will solve your error. For eg SELECT D.DEPTNO AS DNO, D.DNAME AS DNAME.. Like wise..

Edited by ruhi: n/a

0

It isn't solved yet...

1. You have a 3rd table, yet there is no reference in your Where clause.
(This may generate more records than you're expecting.)

2. Your error is being generated by your Group by.
You have a DEPTNO, but do NOT specify from which table.
(ie. Add aliases to all of the columns.)

Edited by hfx642: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.