Home » Developer & Programmer » Forms » ORA-00918: column ambiguously defined
ORA-00918: column ambiguously defined [message #221442] Mon, 26 February 2007 15:21 Go to next message
YouthHelp
Messages: 12
Registered: February 2007
Location: Southfield, MIchigan
Junior Member

FRM-40505: ORACLE error: unable to perform query.
--------------------------------------------------------------
ORA-00918: column ambiguously defined


I am modifying a form which calls for the user to prevent from deleting invoice errors being generated in a form called Invoice Correction Form. I created two blocks called HAP_AR_INTERFACE_ERRORS and BLOCKNAME. Each block will have indicators either invalid or errors listing what caused the data to be rejected. In the Property Palette under Query Data Source Name I have put the tables that will be needed to pull only data needed (hap_ar_invoice_interface_lines, HAP_AR_INTERFACE_ERRORS) and in the WHERE Clause I have added the statement:

HAP_AR_INVOICE_INTERFACE_LINES.STATUS = 'I' AND HAP_AR_INVOICE_INTERFACE_LINES.INVOICE_NUMBER = HAP_AR_INTERFACE_ERRORS.SEGMENT2 OR HAP_AR_INVOICE_INTERFACE_LINES.GROUP_NUM = HAP_AR_INTERFACE_ERRORS.SEGMENT2.

After compiling the form in UNIX and opening the form in Oracle Financials query for related data for errors I get:

FRM-40505: ORACLE error: unable to perform query.
--------------------------------------------------------------
ORA-00918: column ambiguously defined

What am I doing wrong?
Re: ORA-00918: column ambiguously defined [message #221463 is a reply to message #221442] Mon, 26 February 2007 23:35 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It usually means that you should refer to a column using NOT only its name, but also a TABLE NAME (or its alias), because the same column exists in more than one table used in a query.

This would be an example (on Scott's schema):
SQL> select ename, dname
  2  from emp, dept
  3  where deptno = deptno;   --> this is wrong
where deptno = deptno
               *
ERROR at line 3:
ORA-00918: column ambiguously defined


SQL> select e.ename, d.dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
etc.

Note that column might be ambiguously defined not only in the WHERE clause, but as a part of selected column list as well.

It might be fine if you took it as a standard - always use table aliases in your queries.
Previous Topic: converting numbers to hours...helpppp!!
Next Topic: display field not populating
Goto Forum:
  


Current Time: Thu Dec 08 14:12:30 CST 2016

Total time taken to generate the page: 0.10475 seconds