CREATE TABLE failed ORA 00957 Duplicate column name

Question

As I tried to create new table from existing 2 table with specific column name in 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

Can anyone help?

Solution 1

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 using join 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.

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.

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, ...

Solution 2

To be completely honest, that query is a mess. You've got several errors in your SQL statement:

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.

a.ALIDFLAG,b,VALIDFLAG,

I've got a suspicion that this should really be a.VALIDFLAG instead of a.ALIDFLAG. Also, you need to replace b,VALIDFLAG with b.VALIDFLAG.

SELECT a.BILLFREQ    a.CDHRNUM,

You're missing a comma after a.BILLFREQ - this is a syntax error.

a.A‌​GNYCOY,a.AGNTCOY 

There's the culprit - you're selecting the same column twice. Get rid of the second one.

EDIT Actually, 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

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.

Solution 3

ORA-00957: duplicate column name

The only reason for that error in your CTAS statement is that you have similar column name in the SELECT statement. Though you might be referring to different table columns, but you did not use a column alias

Error reproduce:

Using the standard EMP and DEPT table.

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.

Comments

[marc_s]

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged

[a_horse_with_no_name]

The parentheses are totally wrong. Remove them completely

[a_horse_with_no_name]

Please show your real statement, not an obfuscated one. The query that you show us will not result in the error.

[dwan]

As it my client data I am not able to share it

[Alex Poole]

We don't need data, we need the actual column names you're using, so we can see the duplicate that you've removed/hidden in your posted code. You aren't even showing consistent replacements for each column name. It's impossible to help you from what you've shown. "Duplicate column name" couldn't be much clearer, really.

[Lalit Kumar B]

One of those columns listed in the SELECT list are similar. Use proper column alias.

[dwan]

This is my real code , now please help me

[Utsav]

Edited my answer. Check now.

[Frank Schmitt]

Please don't add additional info in comments - EDIT your question instead. Also, your CTAS statement is missing the table name - is this the real * actual* statement you're trying to run?

[Alex Poole]

Also please copy and paste the actual code you're running (as an edit to the question), don't retype it or modify it, as you seem to be introducing more errors and are still apparently hiding the actual problem.

[miracle173]

@marc_s: Maybe its use is discouraged by Aaron Bertrand but a lot of people do not in many situations. I think one can omit this boring 'use ANSI join syntax'

[dwan]

please see my updated query..but still facing same problem

[Utsav]

use start braces before select as I gave

[a_horse_with_no_name]

You don't need to put the select in parentheses. That's just useless noise

[dwan]

@ a_hourse_with_no_name- can you please explain with code where no neeed of select ?

[a_horse_with_no_name]

@dwan: I didn't say there is no need for the select. I said there is no need to put parentheses around the select.

[dwan]

@Utsav- please see updated query, still same error duplicate column name

[Utsav]

Give the output of only your select statement.

[Lalit Kumar B]

@dwan Impossible. Edit your question and show me what you are doing the way I have demonstrated above.

[Utsav]

run only the select statement. What do you get?

[Alex Poole]

Except that isn't the same name twice, at least as shown; AGNY vs AGNT. But I suspect the OP has retyped that code into the comment - since it has so many errors that would stop it executing - and is still hiding the real problem.

[Frank Schmitt]

You're right. That query is so broken that it's difficult to keep track of the errors.

[dwan]

@Frank- As I have type query hereits makes some mistake, I had given table name, column nasme is also ok..its just typepo mistake

[Frank Schmitt]

@dwan PLEASE edit your question and add the REAL query you run. Otherwise we cannot help you.

[dwan]

@Utsav- only with select statement run perfectly

[dwan]

CREATE TABLE DEL_002 AS SELECT a.BILLFREQ a.CDHRNUM, a.CNTTYPE, a.CURRFROM, a.CURRTO,a.JOWNNUM,a.OCCDATE,a.PSTATCODE,a.PSTATDATE,a.STATC‌​ODE,a.STATDATE,a.REG‌​,a.BILLCHNL,a.STATA ,a.STATB,b.ANBCCD,b.CRRCD,b.CRTABLE,b.INSTPREM, b.MORTCLS,b.PCEDATE,b.PCETRM,b.SEX,b.SINGP,b.SUMINS,b.ZBIN,b‌​.ZLIN,a.CHDRPFX,a.CH‌​DRCOY,a.VALIDFLAG AS VALIDFLAG_A,b,VALIDFLAG AS VALIDFLIG_B,b.TRAANO,a.COWNPFX,a.COWNCOY,a.COWNNUM,a.AGNTPFX‌​,a.AGNYCOY,a.AGNTNUM FROM MSTDTA001 a INNER JOIN COVRPF b ON (a.CHDRNUM= b.CHDRNUM AND a.CHDRCOY= b.CHDRCOY)....EDIT code but still same prob

[dwan]

But If I remove one VALIDFLAG from another table then it works perfectly

[Alex Poole]

@dwan - inferring the underlying table structures from your CTAS, this works. I suspect there are other typos, probably hiding other problems, but the same principal will apply.

[dwan]

@FrankSchmitt- thanks for great help....it works perfectly. Its really a very silly mistake

[Ryan]

What if I want to select a.*, b.* since a and b are table with many columns