CODEWITHSUNDEEP
oracle-databaseora-00918
Daniele
Daniele

Reputation: 81

ORA-00918: column ambiguously defined: how to find the column

I'm getting the classic error:

ORA-00918: column ambiguously defined

Usually, I know how to solve it but my problem now is that I'm working with a 700 row query.

Is there a way to identify the column?

Upvotes: 8

Views: 39834

Answers (5)

Ricardo Arnold
Ricardo Arnold

Reputation: 913

Have you tried to do a binary search?

e.g.

If your original query looks like

Select  col1 
       ,col2
       ,col3
       ,col4
from MyTable
  1. you can start with commenting the 2nd half
Select  col1
       ,col2
       /*,col3
       ,col4 */
from MyTable
  1. If you still get the error, run the query again commenting some column from the other half:
Select col1
       /*,col2 */
      ,col3
      ,col4
from MyTable

If you still get an error then your problem is with col1, otherwise you need to change col2.

Upvotes: 7

Nikhil
Nikhil

Reputation: 3950

you can check common columns by using :

 select COLUMN_NAME from ALL_TAB_COLS where TABLE_NAME = 'tablenamefirst'
intersect
select COLUMN_NAME from ALL_TAB_COLS where TABLE_NAME = 'tablenamesecond';

Upvotes: 1

James Lawruk
James Lawruk

Reputation: 31355

In Oracle, you can use all_tab_cols to query the columns names of your tables. The following query will return the common column names between TABLE1 and TABLE2. Then you just need to prefix those common columns instead of all 100 column references.

select column_name from all_tab_cols
where table_name='TABLE1' and owner ='OWNER1' 
and column_name in (
select column_name from all_tab_cols
where table_name='TABLE2' and owner ='OWNER2')

Upvotes: 2

MrHardwick
MrHardwick

Reputation: 1

For posterity's sake: I had this issue when I selected columns TABLE1.DES and TABLE2.DES in a query without aliasing the result. When I ran it alone my SQL editor turned these into DES and DES_1, no complaint.

However when I turned the same query into a subquery

SELECT a.col1, a.col2, a.col3, b.*
from TABLE3 a
INNER JOIN (
--that query as a subquery
) b
on a.PK=b.FK`

it threw the same ORA-00918 error message you described. Changing the SELECT in my subquery to

SELECT TABLE1.DES AS T1_DES, TABLE2.DES AS T2_DES ...

fixed the issue.

Upvotes: 0

APC
APC

Reputation: 146239

The ambiguous column error message indicates that you have joined two (or more) columns in your query which share the same column name.

The proper way to solve this is to give each table in the query an alias and then prefix all column references with the appropriate alias. I agree that won't be fun for such a large query but I'm afraid you will have to pay the price of your predecessor's laxness.

Upvotes: 6

Related Questions