CODEWITHSUNDEEP
sql-serveroracle-database
fattmagan
fattmagan

Reputation: 51

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

The error is oddly specific, but I've just about honed in where it's occurring. The standard fix doesn't seem to be working though.

Here's where the error occurs. It's a big statement, so I'm just posting where I've found the error

 CREATE OR REPLACE VIEW SalesInvoiceDoc 
 AS 
 ( SELECT si.salinv_Num, si.salinv_Terms, si.salinv_SaleDate, 
          es.empName AS SalesAgent, man.empName AS ApprovingManager,
          si.salinv_ApproveDate, ...

 ... FROM service_invoice si
 JOIN employee es
    ON (es.empID = si.salinv_EmpID)
 JOIN employee man
    ON (man.empID = si.salinv_ManID)

Essentially it's a Sales invoice document with a sales agent and approving manager. Both are stored in the employee table with a subtype discriminator set up for 'manager' or 'sales', and views for the subtype children tables to avoid duplication errors.

I've supplied aliases to try and avoid duplicate columns, but when I run this script, I get the duplicate column names error:

ORA-00957: duplicate column name

Does anyone know how to resolve this? Is it even possible? Any help would be greatly appreciated. Thank you in advance!

Edit: here's the full statement

 CREATE OR REPLACE VIEW SalesInvoiceDoc
 AS
 ( SELECT si.salinv_Num, si.salinv_Terms, si.salinv_SaleDate,
    es.empName AS SalesAgent,
    man.empName AS ApprovingManager, si.salinv_ApproveDate,
    sc.custName, sc.custHouse, sc.custCity, 
    sc.custState, sc.custZIP, sc.custPhone, sc.custEmail,
    sv.vehicle_VIN, sv.vehicle_year, sv.vehicle_make, 
        sv.vehicle_model, sv.vehicle_ext_color, sv.vehicle_trim,
        sv.vehicle_list_base_price, sv.vehicle_mileage, sv.vehicle_condition,
        sv.vehicle_description,
     ti.vehicle_make, ti.vehicle_year, ti.vehicle_model, ti.vehicle_VIN, 
        ti.tradein_allowance,
    sv.vehicle_list_base_price "SellingPrice", sv.shipping "Shipping",
        ti.tradein_allowance "TradeAllowance",
(sv.vehicle_list_base_price + sv.shipping - ti.tradein_allowance) "Subtotal",
(sv.vehicle_list_base_price + sv.shipping - ti.tradein_allowance)*.0825 "Taxes",
(sv.vehicle_list_base_price + sv.shipping - ti.tradein_allowance)*1.0825 "TotalSellingPrice"

 FROM sales_invoice si
JOIN employee es
    ON (es.empID = si.salinv_EmpID)
JOIN employee man
    ON (man.empID = si.salinv_ManID)
JOIN customer sc
    ON (sc.custID = si.salinv_CustID)
JOIN vehicle sv
    ON (sv.vehicle_VIN = si.salinv_SalVIN)
LEFT OUTER JOIN vehicle ti
    ON (ti.vehicle_VIN = si.salinv_tiVIN)
 );

Upvotes: 2

Views: 1898

Answers (2)

Rahul
Rahul

Reputation: 77866

Issue is cause sv.vehicle_VIN is appearing twice in your query and so does other columns. See below. You need to alias them all accordingly

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

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

Upvotes: 0

user1641172
user1641172

Reputation:

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.

Upvotes: 1

Related Questions