Ошибка ora 00918 column ambiguously defined

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: колонка неясно (неоднозначно) определена

Причина:

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

Действие:

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

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;

totn Oracle Error Messages


Learn the cause and how to resolve the ORA-00918 error message in Oracle.

Description

When you encounter an ORA-00918 error, the following error message will appear:

  • ORA-00918: column ambiguously defined

Cause

You tried to execute a SQL statement that joined two or more tables, where a column with the same name exists in both tables.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Prefix the column with the table name and then re-execute the statement.

For example, if you tried to execute the following SQL statement:

SELECT supplier_id, quantity
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

You would receive the following error message:

Oracle PLSQL

Since the supplier_id column exists in both the suppliers and orders table, you need to prefix the column with the table name as follows:

SELECT suppliers.supplier_id, quantity
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

in SQL I have these table

create table employee(
emp_ssn number(10),
first_name varchar2(10) not null ,
second_name varchar2(10),
last_name varchar2(10) not null ,
address varchar2(20) ,
birthdate date not null  ,
super_ssn number(10),
job_no  number (2),
constraint employee_pk primary key (emp_ssn));

create table job (
job_no number (2),
job_name varchar2(11) ,
constraint job_pk primary key (job_no ));

and I write a query that display how many employee in each
job using group by

SELECT     job_no, job_name,COUNT(emp_ssn)
FROM       job j JOIN employee e
ON         (j.job_no = e.job_no)
GROUP BY   j.job_no, j.job_name;

and the output is

SELECT     job_no, job_name,COUNT(emp_ssn)
FROM    job j JOIN employee e
ON       (j.job_no = e.job_no)
GROUP BY   j.job_no, j.job_name;
SELECT     job_no, job_name,COUNT(emp_ssn)

ERROR at line 1: ORA-00918: column ambiguously defined

can you help me please !

  • Ошибка openservice failed 1060
  • Ошибка openserver одновременный запуск
  • Ошибка openscmanager failed 0x5
  • Ошибка opengl worms
  • Ошибка ora 00604