Duplicate Column Name Error While Creating View

Duplicate column name error while creating view

You are creating a view with the same column ret_code twice; if you the same column to appera twice in the view, you need to use an alias for one of the two occurrences:

CREATE VIEW ReturnCode90
AS
  SELECT t.ret_code as someAlias,
       TO_CHAR (t.imx_creation_dt, 'DD-MON-YYYY HH24:MI:SS') Created_Date,
       CURRENT_TIMESTAMP Curr_tmp,
       t.* 
  FROM imxbuff.bank_imx_pmt_head t
  WHERE t.ret_Code = '90' 
 order by t.imx_creation_dt desc;

If you don't want this, you can remove the column from the select list

SELECT TO_CHAR (t.imx_creation_dt, 'DD-MON-YYYY HH24:MI:SS') Created_Date,
       CURRENT_TIMESTAMP Curr_tmp,
       t.*

or, better, explicitly write the column names instead of using *:

  SELECT t.ret_code,
       TO_CHAR (t.imx_creation_dt, 'DD-MON-YYYY HH24:MI:SS') Created_Date,
       CURRENT_TIMESTAMP Curr_tmp,
       t.otherColumn1,
       t.otherColumn2,
       ...

SQL Developer duplicate column name while creating view

Just need to add column aliases.

create view vw_sales_may as 
select customers.id as CustomerID, 
orderdetails.id as OrderDetailsID, 
sales.sale_date, 
sales.total_value 
from customers 
inner join sales on customers.id = sales.customer_id 
join orderdetails on orderdetails.customer_id = customers.id 
where to_char(sale_date,'MM') = 05;

How to solve ERROR 1060: Duplicate column name using Views - Create View

Use aliases to assign names:

CREATE VIEW v_payment AS
    SELECT p.payment_id, c.first_name as customer_first_name, c.last_name as customer_last_name,
           s.first_name as staff_first_name, s.last_name as staff_last_name,
           p.amount
    FROM payment p INNER JOIN
         customer c
         ON p.customer_ID = c.customer_ID INNER JOIN
         staff s
         ON p.staff_ID = s.staff_ID;

first_name and last_name appear twice in your select list. The above clarifies whether the name is for a customer or staff.

Duplicate Column error while creating a view

Isn't the error obvious? The view cannot have duplicate column names. When creating the view, you should really list all the columns explicitly.

But, if the only duplicated column is salespersonid, you can use this shortcut:

CREATE VIEW v2 AS 
    SELECT *
    FROM v1 view1 LEFT JOIN
         salesperson sp
         USING (salespersonid);

SQL Duplicate column names error in Create View, even with aliases

You are duplicating column names from 2 different tables:

sv.vehicle_VIN, 
sv.vehicle_year, 
sv.vehicle_make, 
sv.vehicle_model

and

ti.vehicle_make, 
ti.vehicle_year, 
ti.vehicle_model, 
ti.vehicle_VIN

The resulting column name does not include the table alias.

Oracle 11g Duplicate column name error on view with no duplicate columns

Obviously, this query has duplicate columns:

SELECT *
FROM users u INNER JOIN
     states s 
     ON u.state_id = s.state_id INNER JOIN
     accounts a
     ON u.account_id = a.account_id

Among the duplicates are u.state_id, s.state_id, u.account_id, and a.account_id -- the table alias is ignored.

I recommend that you list all the columns that you want from each table. However, if the duplicates are only the JOIN keys, then there is a short-cut -- you can use USING instead of ON:

SELECT *
FROM users u INNER JOIN
     states s 
     USING (state_id) INNER JOIN
     accounts a
     USING (account_id)

Why does Create table show ORA-00957: duplicate column name

It is because tables NS_F3 and NS_FA2 contain columns with the same name - in that case, you should use column alias to avoid duplicate column names.

Here's an example: I'm creating a simple table, as extract of several columns from Scott's EMP table:

SQL> create table t_first as select deptno, empno, ename from emp where rownum < 5;

Table created.

Join it to DEPT table:

SQL> select * from t_first e join dept d on e.deptno = d.deptno;

    DEPTNO      EMPNO ENAME          DEPTNO DNAME          LOC
---------- ---------- ---------- ---------- -------------- -------------
        20       7369 SMITH              20 RESEARCH       DALLAS
        20       7566 JONES              20 RESEARCH       DALLAS
        30       7521 WARD               30 SALES          CHICAGO
        30       7499 ALLEN              30 SALES          CHICAGO
 ^^^^^^^^^                         ^^^^^^^^
 this is DEPTNO column ...         ... and here's another one

As long as it works in SELECT, it won't work in CREATE TABLE:

SQL> create table test as
  2  select * from t_first e join dept d on e.deptno = d.deptno;
select * from t_first e join dept d on e.deptno = d.deptno
       *
ERROR at line 2:
ORA-00957: duplicate column name

The solution is to use a column alias, such as:

SQL> create table test as
  2  select e.deptno emp_deptno,  --> first alias
  3    e.empno,
  4    e.ename,
  5    d.deptno,     dept_deptno  --> second alias
  6    d.dname,
  7    d.loc
  8  from t_first e join dept d on e.deptno = d.deptno;

Table created.

SQL> select * From test;

EMP_DEPTNO      EMPNO ENAME      DEPT_DEPTNO DNAME          LOC
---------- ---------- ---------- ----------- -------------- -------------
        20       7369 SMITH               20 RESEARCH       DALLAS
        20       7566 JONES               20 RESEARCH       DALLAS
        30       7521 WARD                30 SALES          CHICAGO
        30       7499 ALLEN               30 SALES          CHICAGO

SQL>

---

Related Topics