VIEW in SQL

[rotten69]

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"

[ruhi]

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..

[rotten69]

Thanks, buddy..

[ruhi]

You are welcome..Mark it solved na:)

[hfx642]

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.)