Column ambiguously defined oracle ошибка

SELECT  DISTINCT  
        per_all_people_f.EMPLOYEE_NUMBER 
        , MAX(per_all_people_f.LAST_UPDATE_DATE) 
        , per_all_people_f.KNOWN_AS  FULL_NAME
        , to_char(notified_termination_date, 'DD-MM-YYYY') AS termination_date 
        , :FROM_DATE  DATE1
        , :TO_DATE DATE2
--      , D_LEAVING_REASON AS D_LEAVING_REASON
        , CASE substr(substr(hr_all_organization_units_tl.NAME, instr(hr_all_organization_units_tl.NAME, '.') + 1), 1, 1)
        WHEN 'B'  THEN
            'إدارة الاتصالات وتقنية المعلومات'
        WHEN 'C'  THEN
            'إدارة المشاريع'
        WHEN 'D'  THEN
            'الإدارة القانونية'
        WHEN 'E'  THEN
            'إدارة الصحه والسلامة والبيئه'
        WHEN 'F'  THEN
            'إدارة هندسة المكامن والانتاج'
        WHEN 'G'  THEN
            'إدارة الهندسة'
        WHEN 'H'  THEN
            'إدارة العمليات'
        WHEN 'J'  THEN
            'إدارة الحفر وصيانة الآبار'
        WHEN 'K'  THEN
            'إدارة المواد'
        WHEN 'L'  THEN
            'إدارة النقل والخدمات'
        WHEN 'M'  THEN
            'إدارة الاستكشاف'
        WHEN 'N'  THEN
            'إدارة فرع بنغازي'
        WHEN 'P'  THEN
            'إدارة التخطيط'
        WHEN 'R'  THEN
            'إدارة المالية'
        WHEN 'T'  THEN
            'إدارة المراجعه'
        WHEN 'W'  THEN
            'إدارة التدريب والتطوير'
        WHEN 'Y'  THEN
            'إدارة شؤون الموظفين'
            else case  substr(substr(hr_all_organization_units_tl.NAME, instr(hr_all_organization_units_tl.NAME, '.') + 1), 1, 3)
              WHEN 'A11'  THEN
            'لجنة المناقصات'
        WHEN 'A10'  THEN
            'لجنة الادارة'
        WHEN 'A12'  THEN
            'قسم الاعلام '
            end
        END  DEPARTMENT
    , CASE d_leaving_reason 
        WHEN 'Retirement'  THEN
            'التقاعد'
        END 
        LEAVING_REASON1 
FROM    per_all_people_f
LEFT JOIN per_periods_of_service_v ON per_all_people_f.person_id = per_periods_of_service_v.person_id
LEFT JOIN per_assignments_f ON per_all_people_f.EMPLOYEE_NUMBER = per_assignments_f.ASSIGNMENT_NUMBER
LEFT JOIN hr_all_organization_units_tl ON per_assignments_f.ORGANIZATION_ID = hr_all_organization_units_tl.ORGANIZATION_ID
WHERE   notified_termination_date >= TO_DATE(:FROM_DATE,'MM-YYYY') AND notified_termination_date <= TO_DATE(:TO_DATE,'MM-YYYY')
--      AND D_LEAVING_REASON = 'Retirement'
        AND CURRENT_EMPLOYEE_FLAG IS NULL AND employee_number IS NOT NULL
GROUP BY EMPLOYEE_NUMBER,d_leaving_reason,LAST_UPDATE_DATE,KNOWN_AS,notified_termination_date
,:FROM_DATE,:TO_DATE,NAME

I am trying to retrieve some data (coursename) from one of my tables but the following error is coming all the time

ORA-00918: column ambiguously defined

the command I am typing is:

select bookno,courno,coursename
from booking, course,coursename
where bookno = 6200
and booking.courno = course.courno
and coursename.coursenameno = course.coursenameno

I have some tables as described :

CREATE TABLE BOOKING 
    (BOOKNO NUMBER (4) NOT NULL,
     COURNO NUMBER (4) NOT NULL,
     BOOKDATE DATE,
     BOOKCUSTPAYMENT VARCHAR (20),
     CONSTRAINT PK_BOOK PRIMARY KEY (BOOKNO,COURNO),
     CONSTRAINT FK_BOOK FOREIGN KEY (COURNO) REFERENCES COURSE(COURNO)
     ON DELETE CASCADE);

CREATE TABLE CUSTOMER
    (CUSTNO NUMBER (4) NOT NULL, -- creation of primary-key 
     PROFNO NUMBER (4) NOT NULL, 
     CUSTFNAME VARCHAR (15),
     CUSTLNAME VARCHAR (15),
     CUSTDOB DATE,
     CUSTPHONEDAY NUMBER (15),
     CUSTPHONEEVE NUMBER (15),
     CONSTRAINT PK_CUST PRIMARY KEY (CUSTNO),
     CONSTRAINT FK_PROF FOREIGN KEY (PROFNO) REFERENCES PROFICIENCY(PROFNO)
     ON DELETE CASCADE);



CREATE TABLE COURSENAME 
    ( COURSENAMENO NUMBER (4) NOT NULL,
      COURSENAME VARCHAR (20),
      COURSEDESC VARCHAR (120),
      COURSEDAYCOST NUMBER (7,2),
      CONSTRAINT PK_COURSENAME PRIMARY KEY (COURSENAMENO));   


CREATE TABLE COURSE
     (COURNO NUMBER (4) NOT NULL, -- creation of primary-key
      COURSTART DATE,
      COUREND DATE,
      COURSENAMENO NUMBER (4) NOT NULL,
      ACCDAYNO NUMBER (4) NOT NULL,
      FOODNO NUMBER (4) NOT NULL,
      TRANSNO NUMBER (4) NOT NULL, 
      CONSTRAINT PK_COURSE PRIMARY KEY (COURNO),
      CONSTRAINT FK_COURSENAME FOREIGN KEY (COURSENAMENO) REFERENCES COURSENAME(COURSENAMENO));

I am researching but I cannot figure out what is happening !!!

ORA-00918: column ambiguously defined error occurs when a column name in a join exists in more than one table and is thus referenced ambiguously. The ORA 00918 column ambiguously defined error occurs when attempting to join two or more tables with the same name across columns. This column name is referred as an ambiguous reference. If a column with the same name exists in two or more tables, the column name should be prefixed with the table name in joins. Otherwise, the column is identified ambiguously in the join, and the sql query is unable to determine the column name from the tables. In this scenario, the error message ORA-00918: column ambiguously defined will be shown.

The joins in the sql query combine all of the columns from two or more tables. If a column name is used in two or more tables, the column name is ambiguously recognized in the SQL join. Oracle will give an error ORA-00918: column ambiguously defined, if the column name is used to refer. The reference to the column name should be distinguished in some way. There are several methods for uniquely identifying the column names in the join.

When the ORA-00918 error occur

If two or more tables with the same column name are created and joined in a sql query, the column name may be recognized ambiguously. Because the column name is available in all of the join tables, Oracle could not match with any one table to get the data. The error ORA-00918: column ambiguously defined will be thrown in this scenario.

Problem

create table dept(
deptid number primary key,
deptname varchar2(100)
);

create table employee(
id number primary key,
name varchar2(100),
deptid number, foreign key(deptid) references dept(deptid)
);

select * from dept, employee where deptid=1;

Error

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:
Error at Line: 16 Column: 36

Root Cause

If more than one table includes the same column name and refers to those columns in a join, the column name will be ambiguous. Oracle will search in the joined tables if you refer to the column name. If the same column name appears in two or more tables, the column name is identified ambiguously. With those tables, the join could not be performed. There is no way to distinguish the columns.

Solution 1

If the same column name appears in multiple tables and is referenced in a join, the column name becomes ambiguous. In sql joins, the column name is identified ambiguously. It is necessary to differentiate the columns in the joins. One method is to prefix the table name when referring it in joins. The table name is used to uniquely identify the column name.

Problem

create table dept(
deptid number primary key,
deptname varchar2(100)
);

create table employee(
id number primary key,
name varchar2(100),
deptid number, foreign key(deptid) references dept(deptid)
);

select * from dept, employee where deptid=1;

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

Solution

select * from dept, employee where dept.deptid=1;

Solution 2

The column name becomes ambiguous if it occurs in many tables and is referenced in a join. The column name is ambiguously recognized in sql joins. In order to separate the columns in the joins, they must be differentiated. If you use the same table in a sql join again, referencing the column by table name will fail. The table alias should be used to refer to the column name in this situation.

Problem

create table employee(
id number primary key,
name varchar2(100),
managerid number, foreign key(managerid) references employee(id)
);

select * from employee, employee where id=managerid;

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

Solution

select * from employee mgr, employee emp where mgr.id=emp.managerid;

Solution 3

When a column name appears in many tables and is referenced in a join, it becomes confusing. In sql joins, the column name is recognized ambiguously. The columns in the joins must be distinct in order to be separated. You may use a select query to change the column names before using them in joins. The select query will provide a list of unique column names to which you may refer. In the example below a select query is used in the joins.

Problem

create table dept(
deptid number primary key,
deptname varchar2(100)
);

create table employee(
id number primary key,
name varchar2(100),
deptid number, foreign key(deptid) references dept(deptid)
);

select * from dept, employee where deptid=1;

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

Solution

select * from dept, (select deptid departmentid from employee) where deptid=1;

ORA-00918: колонка неясно (неоднозначно) определена

Причина:

имя колонки используемой в объединении используется в более чем одной таблице, и поэтому ссылка понимается неоднозначно. В объединении, любая колонка которая присутствует в более чем одной таблице должна быть префиксирована своим именем таблицы на которую ссылаются. На колонку ссылаются TABLE.COLUMN или TABLE _ALIAS.COLUMN. Например, если соединяются таблицы EMP и DEPT и они обе содержат колонку DEPTNO, то все ссылки на DEPTNO должны быть префиксированы именем таблицы, как EMP.DEPTNO или E.DEPTNO.

Действие:

Префиксируйте ссылки на имена колонок, которые существуют в объединенных таблицах именем таблицы или сокращением имени таблицы, при этом ставьте точку как показано в примере выше.

Ora 00918 column ambiguously defined means that your Oracle database is unable to determine which table a column belongs to because you specified it in more than one table in your query.

This is a rather complex error message to troubleshoot, but this article will teach you why you’re seeing the error and methods that can fix the ambiguity in your query.

After reading this article, you’ll know more about the Oracle database, and you can solve this error if you see it again. That being said, get your query ready and launch your Oracle database because we’re about to teach you how to resolve ORA-00918 column ambiguously defined.

Why Do You Have Ambiguity in Your Column Names?

You have ambiguity in your column names because you’re using a column name multiple times in your SQL query. As a result, the Oracle database cannot know which table the column belongs to, so it throws the ORA-00918 error in response.

– You’re Using a Column Name Multiple Times in Your Query

The ambiguity error in your SQL query occurred because you’re using a column name multiple times. As a result, the database does not know which table the column belongs to, and it throws the “ORA-00918 column ambiguously defined Oracle” error message.

For example, in the following, we have two tables, PC_Mechanic and PC_Fixing_Cost, and both tables have the “mechanic_id” column. Then the “SELECT” statement is trying to find the mechanic that fixed a particular PC, but the query is ambiguous.

That’s because the “mechanic_id” column exists in the PC_Mechanic and the PC_Fixing_Cost table. So, the Oracle database does not know which table the “mechanic_id” belongs to, and it throws the ambiguity error.

How To Fix the Ambiguity in Your Column Names?

To correct ambiguous column names in your SQL query, do any of the following:

• Used the table name to identify the column
• Use table aliases to refer to the column name
• Use a sub-query for unique column names
• Avoid the asterisks sign in your SELECT statements
• Rename the columns

– Used the Table Name To Identify the Column

When an ambiguity error occurs, you can use a table name to identify the column. By doing this, the Oracle database knows where the column belongs and will not raise the ORA-00918 error.

For example, in our PC mechanic example, you can update the “SELECT” statement by adding the table name (“PC_Mechanic”) as the prefix to the first “mechanic_id” in the query. This will prevent the ORA-00918 error, and the code will work as expected.

— Add the table name to differentiate the column

Now, if your SQL is different from the one above, you can use the same approach to resolve the ambiguity error. For example, in the following, we have a “college” and a “staff” table with different table structures.

The error in the code is the “SELECT” statement that’s using the “college_id” in the “WHERE” condition. This should work, but both tables have the “college_id” column that will confuse the database when you run the code.

The solution to the error in the previous SELECT statement is to prefix the college_id in the WHERE clause with the table name.

–Fix: Use the table name of the college_id column

SELECT * FROM college, staff WHERE college.college_id = 1;

– Use Table Aliases To Refer to the Column Name

You can use aliases to solve the “ORA-00918 column ambiguously defined subquery” error, and here, we’ll show you how it works. First, in your Oracle database, you can assign unique names to your column names to prevent an ambiguity error.

For example, in the “SELECT” statement in the following SQL, we want an SQL self-join, but an ambiguity error occurs because we repeated the table name twice.

— This will cause an error

SELECT * FROM staff_tb, staff_tb WHERE id = manager_id;

The fix is to give both table aliases and use them in the SELECT query and with this, the previous SELECT query becomes the following:

SELECT * FROM human_resource mgr, staff_tb emp where mgr.hr_id=emp.id;

Finally, the following variation of the SELECT query also works, and you can use the same to solve the “column ambiguously defined join” error.

– Use a Subquery for Unique Column Names

In Oracle SQL, subqueries can solve the “column ambiguously defined SQL” error in your Oracle database. Generally, a subquery provides a means to retrieve data that will be used in the main query as a filter condition.

Here, you’ll use them to assign a unique column name to your column so that the Oracle database can differentiate it from other columns with the same name. To show how this works, the “SELECT” query in the following code will cause the ORA-00918 error because the “school_id” column is present in the “school” and “principals” tables.

— The following will not work.

SELECT * FROM school, principals WHERE school_id=1;

We can fix this error by using a sub-query that assigns a new name to the “school_id” column of the “principals” table:

SELECT * FROM school, (SELECT school_id schoolid FROM principals) WHERE school_id=1;

– Avoid the Asterisks Sign In Your “Select” Statements

If you can avoid asterisks in your SQL code, please do, but in certain cases, you’ll need them as shown in our previous “column ambiguously defined examples. What’s more, the following are reasons why you might want to avoid asterisks in your SQL query, especially in a production environment:

• Performance: When you use an asterisk in a SQL query, you are asking the database to retrieve all columns in the table. This can be very inefficient, especially if the table has many columns.
• Future changes: If the table schema changes and new columns are added, using an asterisk can result in unexpected results. For example, your queries can start retrieving columns that you don’t want.
• Security: Using asterisks can potentially give users more access to data than they should have if your application has any security concerns.

– Rename the Columns

Renaming your table columns should be your last resort if all else fails. In Oracle database, you can rename a column using the “ALTER TABLE” statement with the “RENAME COLUMN” clause.

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

For example, the following SQL will rename the column “salary” in the table “employees” to “monthly_salary.

ALTER TABLE employees RENAME COLUMN salary TO monthly_salary;

Finally, note that renaming a column can cause data type or length changes. So, this can require additional changes to dependent objects, such as indexes and views.

As a result, we advise that you test the impact of renaming a column before implementing it in a production environment.

Conclusion

This article explained why Oracle databases throw the ORA-00918 error and how you can modify your SQL queries to prevent the error. We’ll leave you with the following summary:

• Multiple columns in your SQL queries is the reason why you see “ORA-00918 column ambiguously defined” is Oracle database.
• To solve “ORA-00918 column ambiguously defined”, use the table name to differentiate the column or use table aliases.
• You can also use sub-queries to assign unique names to your columns if there is a possibility of ORA-00918 error.
• If possible, avoid asterisks in your “SELECT” statements and be specific about what you need from your Oracle database.
• The last resort to solve ORA-00918, is to rename your column using “ALTER TABLE” with the “RENAME COLUMN” clause.

That ends our discussion of ORA-00918 in Oracle databases and how you can resolve it. Every Oracle database developer that’s seeing this error will appreciate it if you show them this article, so kindly do that.