Home » Other » Client Tools » ORA-00918 : column ambiguously defined. Please help!
ORA-00918 : column ambiguously defined. Please help! [message #38034] Thu, 14 March 2002 07:41 Go to next message
Nina
Messages: 113
Registered: March 2000
Senior Member
Running this query results in the ORA-00918 : column ambiguously defined error.
Any ideas what is casing it?

SELECT
pebempl_bcat_code,pebempl_ecls_code,
ptrecls_pict_code,pdrdedn_opt_code1,
to_char(perdhis_effective_date, 'DD-MON-YYYY'),
perdhis_open_enroll_ind,
to_char(perdhis_capture_date, 'DD-MON-YYYY'),
PTRBDCA_BDTY_CODE
FROM payroll.pebempl, payroll.ptrecls,
payroll.pdrdedn,payroll.pdrdedn y , payroll.perdhis,
payroll.ptrbdca
where y.pdrdedn_pidm = spriden_pidm
and pebempl_pidm = perdhis_pidm
and perdhis_capture_date between to_date('01-JAN-2002', 'DD-MON-YYYY')
and to_date('01-FEB-2002', 'DD-MON-YYYY')
and pebempl_ecls_code = ptrecls_code
and pdrdedn_bdca_code = ptrbdca_code
and pdrdedn_bdca_code = perdhis_bdca_code
and perdhis_status = y.pdrdedn_status
and y.PDRDEDN_EFFECTIVE_DATE = (select max(x.pdrdedn_effective_date)
from pdrdedn x
where x.pdrdedn_pidm = y.pdrdedn_pidm
and x.pdrdedn_bdca_code = y.pdrdedn_bdca_code
and x.pdrdedn_status = y.pdrdedn_status)

This line is apparently causing the error:
and pdrdedn_bdca_code = perdhis_bdca_code

thank you
Re: ORA-00918 : column ambiguously defined. Please help! [message #38035 is a reply to message #38034] Thu, 14 March 2002 07:52 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
One of the unqualified columns (no alias or tablename in front) appears in more than one of your tables in the FROM clause. You will need to qualify this column (tablename.column or alias.column).

Actually, it is a good idea to qualify every column in a multi-table join regardless of whether or not there are duplicate column names.
Re: ORA-00918 : column ambiguously defined. Please help! [message #38038 is a reply to message #38034] Thu, 14 March 2002 11:47 Go to previous messageGo to next message
Nina
Messages: 113
Registered: March 2000
Senior Member
Thank you, that worked!
Re: ORA-00918 : column ambiguously defined. Please help! [message #38647 is a reply to message #38034] Fri, 03 May 2002 02:37 Go to previous message
Jochen Fonck
Messages: 1
Registered: May 2002
Junior Member
ORA-00918: column ambiguously defined

Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.
Previous Topic: set escape ON in Stored Proc
Next Topic: splitting and associating part of a field
Goto Forum:
  


Current Time: Thu Mar 28 04:49:15 CDT 2024