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?

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