oracle ORA-00918: 列定义不明确：如何查找列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12461534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00918: column ambiguously defined: how to find the column
提问by Daniele
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?
我收到经典错误“ORA-00918：列有歧义定义”，通常我知道如何解决它，但我现在的问题是我正在处理 700 行查询。有没有办法识别列？
Thx Daniele
谢谢丹尼尔
回答by Ricardo Arnold
Have you tried to do a binary search?
您是否尝试过进行二分查找？
e.g.
例如
If your original query looks like
Select col1
,col2
,col3
,col4
from MyTable
如果您的原始查询看起来像
Select col1
,col2
,col3
,col4
from MyTable
you can start with commenting the 2nd half
Select col1 ,col2 /*,col3 ,col4 */ from MyTableIf you still get the error, run the query again commenting some column from the other half:
你可以从评论第二部分开始
Select col1 ,col2 /*,col3 ,col4 */ from MyTable如果仍然出现错误，请再次运行查询并注释另一半的某些列：
Select col1
/*col2 */
col3
col4
from MyTable
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
如果您仍然收到错误，则您的问题出在 col1 上，否则您需要更改 col2
回答by APC
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.
解决这个问题的正确方法是给查询中的每个表一个别名，然后用适当的别名作为所有列引用的前缀。我同意这么大的查询不会很有趣，但恐怕您将不得不为前任的松懈付出代价。
回答by James Lawruk
In Oracle, you can use all_tab_colsto query the columns names of your tables. The following query will return the common column names between TABLE1and TABLE2. Then you just need to prefix those common columns instead of all 100 column references.
在 Oracle 中，您可以使用all_tab_cols来查询表的列名。以下查询将返回TABLE1和TABLE2之间的公共列名。然后您只需要为这些公共列添加前缀而不是所有 100 个列引用。
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')
回答by nikhil sugandh
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';
回答by MrHardwick
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.
为后人着想：当我在查询中选择列 TABLE1.DES 和 TABLE2.DES 而不给结果添加别名时，我遇到了这个问题。当我单独运行它时，我的 SQL 编辑器将这些转换为 DES 和 DES_1，没有任何抱怨。
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
它抛出了与您描述的相同的 ORA-00918 错误消息。将我的子查询中的 SELECT 更改为
SELECT TABLE1.DES AS T1_DES, TABLE2.DES AS T2_DES ...
SELECT TABLE1.DES AS T1_DES, TABLE2.DES AS T2_DES ...
fixed the issue.
解决了这个问题。
