Ошибка ora 00918

Getting ORA-00918: column ambiguously defined: running this SQL: SELECT * FROM (SELECT DISTINCT(coaches.id), people.*, users.*, coaches.* FROM "COACHES" INNER JOIN people ON peop...
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

Источник

В этом посту вы узнаете, что значит ошибка «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) выдаёт такую ошибку:

Ошибка ORA-00918: column ambiguously defined

Скриншот 1: Ошибка ORA-00918: column ambiguously defined

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

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 выдаёт при такой ошибке следующее сообщение:

Скриншот 2: MySQL #1052 - Column 'number_column' in field list is ambiguous

Скриншот 2: Ошибка MySQL #1052 — Column in field list is ambiguous

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

Источник

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;
Источник
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

Источник
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

Источник

oracle tutorial webinars

ORA-00918

A major source of error across Oracle database systems derives from the table aspect of the software. Even for those of you that may only have database experience confined to the use of basic Microsoft Excel tables, you will know just how frequently mistakes can occur when inserting values, equations, and editing rows and columns. In this sense, Oracle is no different in that these same components can easily lead to errors developing. The ORA-00918 message constitutes just one of these types of errors.

Cause

The source of an ORA-00918 error comes from a mistake when attempting to join two or more tables that share the same name in across columns. This is often referred to as an ambiguous reference. Fortunately it is because of the nature of the action that diagnosing this kind of error is fairly straightforward. Typically you will attempt to merge the tables and it is at this point that the error will initiate, so you should know exactly where the problem is coming from.

Solution

Luckily the solution to this Oracle error is just about as straightforward as finding what causes the problem. What is needed is to add the prefix to each column with the table name that it originally belonged too and then re-execute the SQL statement. For example, if you had the tables POS and PAY being joined, with each containing a column by the name of DAT, then any time DAT is used it needs to be prefixed by its table name, such as POS.DAT or PAY.DAT.

To further illustrate, suppose you had to execute the following SQL statement in order to join two tables:

SELECT date_start, pos

FROM employees, management

WHERE employees.date_start = management.date_start;

This would return an ORA-00918 error, that will simply state “column ambiguously defined”. So what do you do next? You add a prefix to the column with the table name. For the above example, it would look something like this:

SELECT employees.date_start, pos

FROM employees, management

WHERE employees.date_start = management.date_start;

This method will solve the error and also provide more detail when you are scanning data on the fly.

Looking forward

So the error is relatively easy to diagnose and solve, so where’s the hard part? Well, there really isn’t one. Once you solve this error once, odds are you will remember how to quickly solve it in the future. However, the unfortunate aspect of the ORA-00918 is that there aren’t many preventative measures that can be taken. To keep it from springing up, the only thing you can really do is remain aware of the names that you give columns and pre-attach prefixes to common column names. If you find that you’re still having trouble resolving this issue over time and aren’t sure as to what steps you should take next, perhaps contact a licensed Oracle consultant to inquire about further direction.

Источник

What is an ambiguous column name error in SQL?

SQL ambiguous column name is one most common error we are facing while performing a join query with two or more tables, this type of error occurs when we try to retrieve data from two or more tables using SQL join and more than one tables have the same column name appears in the selection.

What is the cause of this error?

The root cause of this error is the same column name in two or more tables and selecting the same column with the same name when performing a join

According to Oracle documents

ORA-00918 column ambiguously defined

  • Cause of error: When the same column name exists in more than one table in a join than one table and is thus referenced ambiguously.
  • Action: To overcome this error we need to prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.),

ALSO READ: SQL FULL OUTER JOIN Explained [Practical Examples]

Example of SQL ambiguous column name

Create two tables for patient and doctor as follow

patient_table(patient_id,patient_name,doctor_id,city)

patient_id patient_name doctor_id city
101 Rekha 11 Surat
102 Reema 12 Vapi
103 Jaya 13 Navasari

doctor_table(doctor_id,doctor_name,city)

doctor_id doctor_name city
11 Rahul Surat
12 Prashant Vapi
13 Asif Navasari

Next create PATIENT Table

Create table patient_table
(
   patient_id int primary key, 
   patient_name varchar(20),
   doctor_id int,
   city varchar(20)
)

Next create DOCTOR Table

Create table doctor_table
(
   doctor_id int primary key, 
   doctor_name varchar(20),
   city varchar(20)
)

Example 1: Write SQL query to display all patient data with doctor id and doctor city name

select patient_id as 'Patient ID' ,patient_name as 'Patient Name',doctor_id as 'Doctor ID',city as 'Doctor City' from patient_table , doctor_table where patient_table.doctor_id=doctor_table.doctor_id
  • In the above query, we used an inner join between two tables patient and doctor to retrieve data from both the tables
  • In the above query we specify city and doctor_id columns, both the columns are common in both the tables so we will get an ambiguous error for both the columns

OUTPUT:
Solved: SQL ambiguous column name [100% Working]
Error Message :

Msg 209,Level 16,State 1,Line 1
Ambiguous column name 'doctor_id'
Msg 209,Level 16,State 1,Line 1
Ambiguous column name 'city'

Solve the “Ambiguous Column Name” Error in SQL

To solve the ambiguous column name error we need to prefixed column name by its table name when referenced with the SQL select statement, the column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN

ALSO READ: SQL Replace Function Explained [Practical Examples]

Example 2 : Write SQL query to display all patient data with doctor id and doctor city name , also specify table name with column name in the SQL select statement

select patient_table.patient_id as 'Patient ID' ,patient_table.patient_name as 'Patient Name',doctor_table.doctor_id as 'Doctor ID',doctor_table.city as 'Doctor City' from patient_table , doctor_table where patient_table.doctor_id=doctor_table.doctor_id

OUTPUT:

Solved: SQL ambiguous column name [100% Working]

Summary

In this article on SQL ambiguous column names, we have covered what is ambiguous column name error in SQL, the cause of ambiguous column name error, how this error explained in oracle document ORA-00918 and also explained SSQLambiguous column name error with practical examples.

References

SQL joins

Read More

SQL Ambiguous Column Name

Related Keywords: ambiguous column name join, sql ambiguous column name, ambiguous column name, ambiguous column name sql, column reference is ambiguous, ms sql ambiguous column name, sql query ambiguous column name

Didn’t find what you were looking for? Perform a quick search across GoLinuxCloud

Источник

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Ошибка netlogon 5722
  • Ошибка netio sys
  • Ошибка netbt 4321 windows 7
  • Ошибка ora 00257 archiver error
  • Ошибка net start при запуске windows

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии