ORA-00918: column ambiguously defined in SELECT *
Getting ORA-00918: column ambiguously defined: running this SQL:
SELECT *
FROM
(SELECT DISTINCT(coaches.id),
people.*,
users.*,
coaches.*
FROM "COACHES"
INNER JOIN people ON people.id = coaches.person_id
INNER JOIN users ON coaches.person_id = users.person_id
LEFT OUTER JOIN organizations_users ON organizations_users.user_id = use开发者_开发知识库rs.id
) WHERE rownum <= 25
Any suggestions please?
A query's projection can only have one instance of a given name. As your WHERE clause shows, you have several tables with a column called ID. Because you are selecting *
your projection will have several columns called ID. Or it would have were it not for the compiler hurling ORA-00918.
The solution is quite simple: you will have to expand the projection to explicitly select named columns. Then you can either leave out the duplicate columns, retaining just (say) COACHES.ID or use column aliases: coaches.id as COACHES_ID
.
Perhaps that strikes you as a lot of typing, but it is the only way. If it is any comfort, SELECT *
is regarded as bad practice in production code: explicitly named columns are much safer.
You have multiple columns named the same thing in your inner query, so the error is raised in the outer query. If you get rid of the outer query, it should run, although still be confusing:
SELECT DISTINCT
coaches.id,
people.*,
users.*,
coaches.*
FROM "COACHES"
INNER JOIN people ON people.id = coaches.person_id
INNER JOIN users ON coaches.person_id = users.person_id
LEFT OUTER JOIN organizations_users ON organizations_users.user_id = users.id
WHERE
rownum <= 25
It would be much better (for readability and performance both) to specify exactly what fields you need from each of the tables instead of selecting them all anyways. Then if you really need two fields called the same thing from different tables, use column aliases to differentiate between them.
You can also see this error when selecting for a union where corresponding columns can be null.
select * from (select D.dept_no, D.nullable_comment
from dept D
union
select R.dept_no, NULL
from redundant_dept R
)
This apparently confuses the parser, a solution is to assign a column alias to the always null column.
select * from (select D.dept_no, D.comment
from dept D
union
select R.dept_no, NULL "nullable_comment"
from redundant_dept R
)
The alias does not have to be the same as the corresponding column, but the column heading in the result is driven by the first query from among the union members, so it's probably a good practice.
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
精彩评论