oracle CREATE TABLE 失败 ORA 00957 列名重复
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33751468/
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
CREATE TABLE failed ORA 00957 Duplicate column name
提问by dwan
As I tried to create new table from existing 2 table with specific column name in oracle.
当我尝试从现有的 2 个表中创建新表时，在 oracle 中具有特定的列名。
I tried below code
我试过下面的代码
CREATE TABLE D_T1
AS
SELECT a.col1, a.col2, a.col3, a.col4, a.col5, b.col6, b.col7, b.col8
FROM db1.table1 a INNER JOIN db1.table2 b
ON (a.col1 = b.colNum AND a.col2 = b.colnum1)
But I get error
但我得到错误
CREATE TABLE failed ORA 00957 Duplicate column name
CREATE TABLE 失败 ORA 00957 列名重复
Can anyone help?
任何人都可以帮忙吗？
回答by Alex Poole
Ignoring the other errors you seem to have introduced by retyping the code, you've shown that you do have a duplicate column, which is what the error is telling you:
忽略您似乎通过重新键入代码引入的其他错误，您已经表明您确实有一个重复的列，这就是错误告诉您的内容：
a.VALIDFLAG, b.VALIDFLAG
You seem to be under the impression that the table (alias) prefix makes the column names in the projection unique. They do not. The table prefix tells Oracle which table to get the column value from (unless you're using the usingjoin syntax, which you are not). If the column appears in two tables you have to prefix the column name with the table. If you want the value from both tables you have to prefix both of them.
您似乎认为表（别名）前缀使投影中的列名唯一。他们不。表前缀告诉 Oracle 从哪个表获取列值（除非您正在使用using连接语法，而您没有使用）。如果该列出现在两个表中，则必须在该表的列名前加上前缀。如果您想要两个表中的值，您必须为它们加上前缀。
With a simple query then referring to both table columns without column aliases is OK, but something trying to consume the result set might struggle. This is fine:
通过一个简单的查询，然后引用没有列别名的两个表列是可以的，但是尝试使用结果集的东西可能会很困难。这很好：
select a.dummy, b.dummy
from dual a
join dual b on b.dummy = a.dummy;
DUMMY DUMMY
------- -------
X X
But notice that both columns have the same heading. If you tried to create a table using that query:
但请注意，两列具有相同的标题。如果您尝试使用该查询创建表：
create table x as
select a.dummy, b.dummy
from dual a
join dual b on b.dummy = a.dummy;
You'd get the error you see, ORA-00957: duplicate column name.
你会得到你看到的错误，ORA-00957：重复的列名。
If you alias the duplicated columns then the problem goes away:
如果您为重复的列设置别名，那么问题就会消失：
create table x as
select a.dummy as dummy_a, b.dummy as dummy_b
from dual a
join dual b on b.dummy = a.dummy;
So in your case you can alias those columns, if you need both:
因此，在您的情况下，如果您同时需要这两个列，您可以为这些列设置别名：
..., a.VALIDFLAG AS validflag_a, b.VALIDFLAG AS validflag_b, ...
回答by Frank Schmitt
To be completely honest, that query is a mess. You've got several errors in your SQL statement:
老实说，这个查询一团糟。您的 SQL 语句中有几个错误：
CREATE TABLE AS SELECT
The table name is missing - this should be
缺少表名 - 这应该是
CREATE TABLE my_new_table AS SELECT
to create a new table named my_new_table.
创建一个名为 my_new_table 的新表。
a.ALIDFLAG,b,VALIDFLAG,
I've got a suspicion that this should really be a.VALIDFLAGinstead of a.ALIDFLAG. Also, you need to replace b,VALIDFLAGwith b.VALIDFLAG.
我怀疑这真的应该是a.VALIDFLAG而不是a.ALIDFLAG. 此外，您需要替换b,VALIDFLAG为b.VALIDFLAG.
SELECT a.BILLFREQ a.CDHRNUM,
You're missing a comma after a.BILLFREQ- this is a syntax error.
后面缺少逗号a.BILLFREQ- 这是语法错误。
a.A??GNYCOY,a.AGNTCOY
There's the culprit - you're selecting the same column twice. Get rid of the second one.
罪魁祸首是 - 您两次选择同一列。摆脱第二个。
EDITActually, the names are different, so this isn't the cause of the error (unless you've mistyped your query in the comment instead of copy& paste).
编辑实际上，名称是不同的，所以这不是错误的原因（除非你在评论中错误地输入了你的查询而不是复制和粘贴）。
To debug this kind of errors, try to
要调试此类错误，请尝试
- format your SQL statement in a readable way
- comment out everything but one column, run the statement and ensure it works
- add one column
- repeat until you find the error or you've added all columns
- 以可读的方式格式化您的 SQL 语句
- 注释掉除一列之外的所有内容，运行该语句并确保其有效
- 添加一列
- 重复直到找到错误或添加了所有列
2ND UPDATE
第二次更新
With the updated query, the error is here:
使用更新的查询，错误在这里：
a.VALIDFLAG,
b,
VALIDFLAG,
You have two columns named VALIDFLAG - use an alias for one of these, and it should work.
您有两列名为 VALIDFLAG - 为其中之一使用别名，它应该可以工作。
回答by Lalit Kumar B
ORA-00957: duplicate column name
ORA-00957: 重复的列名
The only reason for that error in your CTASstatement is that you have similar column name in the SELECTstatement. Though you might be referring to different table columns, but you did not use a column alias
CTAS语句中出现该错误的唯一原因是您在SELECT语句中具有相似的列名。虽然您可能指的是不同的表列，但您没有使用列别名
Error reproduce:
错误重现：
Using the standard EMPand DEPTtable.
使用标准EMP和DEPT表格。
SQL> CREATE TABLE D_T1 AS
2 SELECT a.deptno,
3 b.deptno
4 FROM emp A
5 INNER JOIN dept b
6 ON (a.deptno = b.deptno);
b.deptno
*
ERROR at line 3:
ORA-00957: duplicate column name
Workaround:
解决方法：
Use proper alias:
使用正确的别名：
SQL> CREATE TABLE D_T1 AS
2 SELECT a.deptno e_deptno, --add column alias
3 b.deptno d_deptno --add column alias
4 FROM emp a
5 INNER JOIN dept b
6 ON (a.deptno = b.deptno);
Table created.
回答by Utsav
Edit as per new input from OP.
根据来自 OP 的新输入进行编辑。
You have some syntax error in your query. Corrected those. I also checked for duplicate columns. There are none. Now run this and let me know if you still get same error.
您的查询中有一些语法错误。纠正了那些。我还检查了重复的列。没有了。现在运行它，如果您仍然遇到相同的错误，请告诉我。
CREATE TABLE D_T1 AS SELECT
a.B, a.C,a.C, a.C, a.C,a.J, a.O,
a.P,a.P,a.S,a.S,a.R,a.B,
a.S,a.S,b.A,b.C,b.C,b.I, b.M,
b.P,b.P,b.S,b.S,b.S,b.Z,b.Z,a.C,
a.CH??,a.G,b,V,b.T,a.C,a.C,a.C,
a.A,a.A??Ga.AG
FROM tbl1 a JOIN tbl2 b
ON (a.C= b.C AND a.C1= b.C1)
