CODEWITHSUNDEEP
sqlplsql
Katherine
Katherine

Reputation: 573

PL/SQL - Joining 3 tables get error column ambiguously defined

I'm joining 3 tables in PL/SQL, the employees, departments and another employees for the managers. But i get this error when i run the script

Error report:
ORA-06550: line 19, column 9:
PL/SQL: ORA-00918: column ambiguously defined
ORA-06550: line 9, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I'm new in PL/SQL so can't understand what those error means.

Here is the select statement inside the begin block where i get the error

   select e.first_name||' '||e.last_name AS Employee ,
          m.first_name||' '||m.last_name AS Manager ,
          e.job_id AS jobemp , 
          d.department_name AS Department ,
          e.salary AS Salary
  into ename, manager, jobid, depn, sal
  from employees e join departments d 
          on (e.department_id = d.department_id)
        join employees m
          on (e.manager_id = m.employee_id)
  where salary = (select min(salary) from employees)
  ;

Anyone who could help me identify this error? thanks in advance.

Upvotes: 0

Views: 1817

Answers (1)

podiluska
podiluska

Reputation: 51494

Specify which table salary comes from in your where clause

    select e.first_name||' '||e.last_name AS Employee , 
          m.first_name||' '||m.last_name AS Manager , 
          e.job_id AS jobemp ,  
          d.department_name AS Department , 
          e.salary AS Salary 
  into ename, manager, jobid, depn, sal 
  from employees e join departments d  
          on (e.department_id = d.department_id) 
        join employees m 
          on (e.manager_id = m.employee_id) 
  where e.salary = (select min(salary) from employees);

Upvotes: 4

Related Questions