Ora 00918 column ambiguously defined как исправить

На самом деле, ответ @Fix не совсем правильный (даже почти неправильный). Один и тот же алиас для таблицы использовать два раза можно:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select t.a, t.b from 
(select 1 a from dual) t,
(select 2 b from dual) t;

         A          B
---------- ----------
         1          2

SQL> 

Главное – уникальными должны быть названия столбцов. В запросе выше, например, их надо перечислять явно, через * он уже не работает:

SQL> select * from 
(select 1 a from dual) t,
(select 2 b from dual) t;
select * from
       *
ERROR at line 1:
ORA-00918: column ambiguously defined

SQL> 

У вас проблема была в том, что вы взяли одну и ту же таблицу 2 раза, а так так столбцы там тоже одинаковые, то СУБД не поняла, что вы от нее хотите. Одну и ту же таблицу можно (а иногда и нужно) джойнить с собой, и вот в этом случае алиасы должны быть разные.
Если посмотреть документацию, там написано про столбцы следущее:

If two or more tables have some column names in common, and if you are specifying a join in the FROM clause, then you must qualify column names with names of tables or table aliases.

В то же время в разделе t_alias написано:

Specify a correlation name, which is an alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.

И ни слова про уникальность.

В этом посту вы узнаете, что значит ошибка «ORA-00918: column ambiguously defined» и как её решить. Ошибка возникает, когда при объединении в двух таблицах присутствуют колонки с одинаковым названием и непонятно, к какой таблице относится колонка.

Для воспроизведения ошибки, создаём две простых таблицы с одинаковыми колонками — цифровой и текстовой. И number_column, и text_column присутствуют в обоих таблицах.

CREATE TABLE test_table1(number_column NUMBER, text_column VARCHAR2(50) )

CREATE TABLE test_table2(number_column NUMBER, text_column VARCHAR2(50) )

Выпоняем запрос SQL с объединением через JOIN, выбираем значения number_column, text_column из таблиц test_table1 и test_table2, в которых number_column из одной равняется number_column из другой, и number_column равняется единице.

SELECT number_column, text_column FROM test_table1 JOIN test_table2 ON number_column = number_column WHERE number_column = 1

Уже прочитав предложение сразу становится понятным, что невозможно определить к какой из двух таблиц относится number_column, а также text_column, что менее очевидно. После выполнения запроса Apex SQL Workshop (или любой другой инструмент для работы с базами данных Oracle) выдаёт такую ошибку:

Исправить ситуацию можно двумя методами. В первом просто прописывает название таблицы перед названием колонки.

SELECT test_table1.number_column, test_table1.text_column FROM test_table1 JOIN test_table2 ON test_table1.number_column = test_table2.number_column WHERE test_table1.number_column = 1

Второй метод удобнее. В нём используются алиасы названий таблиц, в нашем примере t1 для test_table1 и t2 для test_table2.

SELECT t1.number_column, t1.text_column FROM test_table1 t1 JOIN test_table2 t2 ON t1.number_column = t2.number_column WHERE t1.number_column = 1

Кстати, в MySQL эта ошибка называется «#1052 — Column ‘number_column’ in field list is ambiguous» и лечится тем же способом. phpMyAdmin выдаёт при такой ошибке следующее сообщение:

Понравился пост? Поделись в соцсетях и подписывайся на аккаунты в Twitter и Facebook!

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;