ORA-00918: the column is ambiguous: how to find the column

Question

I get the classic error "ORA-00918: the column is ambiguously defined", I usually know how to solve it, but now my problem is that I work with the query on line 700. Is there a way to identify the column?

thanks Daniele

Answer 1

Have you tried performing a binary search?

eg.

If your original request looks Select col1 ,col2 ,col3 ,col4 from MyTable

• you can start by commenting on the second half of Select col1 ,col2 /*,col3 ,col4 */ from MyTable

• If you still get the error, run the query by commenting again on some column from the other half:

Select col1 /*col2 */ col3 col4 from MyTable

If you still get the error message, then your problem is related to col1, otherwise you need to change col2

Answer 2

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

The correct way to solve this problem is to provide each table in the query with an alias, and then the prefix of all column references with the corresponding alias. I agree that it will not be fun for such a big request, but I am afraid that you will have to pay a price for the weakness of your predecessor.

Answer 3

For posterity: I had this problem when I selected the columns TABLE1.DES and TABLE2.DES in a query without overlaying the results. When I ran it alone, my SQL editor turned them into DES and DES_1, without complaint.

However, when I included the same query in 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` 

he threw the same ORA-00918 error message that you described. Change SELECT in my subquery to

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

fixed problem.

Answer 4

In Oracle, you can use all_tab_cols to query for the column names of your tables. The following query returns common column names between TABLE1 and TABLE2. Then you just need to prefix these common columns instead of 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')