Member Avatar for 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"

  • 3 Contributors
  • 4 Replies
  • 103 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by hfx642
Member Avatar for 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..

Edited by ruhi because: n/a
Member Avatar for rotten69

Thanks, buddy..

Member Avatar for ruhi

You are welcome..Mark it solved na:)

Edited by ruhi because: n/a
Member Avatar for 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.)

Edited by hfx642 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.