ORA-00918: column ambiguously defined

ORA-00918: column ambiguously defined

If you are using the join condition between two tables then both table having the same name column then following error occurred:


SQL> select first_name , department_id from hr.employees emp , hr.departme
nts dept where emp.department_id = dept.department_id;
select first_name , department_id from hr.employees emp , hr.departments d
ept where emp.department_id = dept.department_id
*
ERROR at line 1:
ORA-00918: column ambiguously defined

Solution:

In the we are using two table EMPLOYEES and DEPARTMENTS having one common column DEPARTMENT_ID which caused the error in SQL Statements. We need to use alias name or table reference with the column name. It will fixed the issue.

In following example, when we executed the above query we got the error because in SELECT Query we miss the use of table reference or table alias in front of using that common column. It make difficult for Oracle to understand that column belong to which table.

Highlighted the mistake done in the previous Query.
Use table alias or table reference in front of the common column


SQL> select first_name , emp.department_id from hr.employees emp , hr.departments dept where emp.department_id = dept.department_id;

FIRST_NAME DEPARTMENT_ID
---------- -------------
Ellen 80
Sundar 80

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s