SQL Join Views - Duplicate Field

Question

I am trying to join two views, I have tried to alias the cAuditNumber field under StkSalesUpdated view to AuditNumber1, but I still get the following message:

Msg 4506, Level 16, State 1, Procedure KFF_Sales_Data_Updated, Line 3 Column names in each view or function must be unique. Column name 'cAuditNumber' in view or function 'KFF_Sales_Data_Updated' is specified more than once.

Below is my SQL statement:

CREATE VIEW KFF_Sales_Data_Updated
AS
SELECT CustSalesUpdated.cAuditNumber
   ,CustSalesUpdated.Account
   ,CustSalesUpdated.cAuditNumber
   ,CustSalesUpdated.Name
   ,StkSalesUpdated.cAuditNumber as AuditNumber1
   ,StkSalesUpdated.Code
   ,StkSalesUpdated.Credit
   ,StkSalesUpdated.Debit
   ,StkSalesUpdated.Description_1
   ,StkSalesUpdated.Id
   ,StkSalesUpdated.ItemGroup
   ,StkSalesUpdated.Quantity
   ,StkSalesUpdated.Reference
   ,StkSalesUpdated.TxDate
FROM CustSalesUpdated
INNER JOIN StkSalesUpdated
ON StkSalesUpdated.cAuditNumber = CustSalesUpdated.cAuditNumber

Answer 1

You have two instances of cauditnumber at position 1 and 3, you need to alias or remove one.

CREATE VIEW KFF_Sales_Data_Updated
AS
SELECT CustSalesUpdated.cAuditNumber -- HERE
   ,CustSalesUpdated.Account
   ,CustSalesUpdated.cAuditNumber --HERE
   ,CustSalesUpdated.Name
   ,StkSalesUpdated.cAuditNumber as AuditNumber1
   ,StkSalesUpdated.Code
   ,StkSalesUpdated.Credit
   ,StkSalesUpdated.Debit
   ,StkSalesUpdated.Description_1
   ,StkSalesUpdated.Id
   ,StkSalesUpdated.ItemGroup
   ,StkSalesUpdated.Quantity
   ,StkSalesUpdated.Reference
   ,StkSalesUpdated.TxDate
FROM CustSalesUpdated
INNER JOIN StkSalesUpdated
ON StkSalesUpdated.cAuditNumber = CustSalesUpdated.cAuditNumber

Answer 2

Maybe it's not because of your renaming or the join, but because you select it twice from the same table? (first 3 lines of the select statement)

SELECT CustSalesUpdated.cAuditNumber  
,CustSalesUpdated.Account
,CustSalesUpdated.cAuditNumber

Answer 3

Same column name specified mulitple times in the SELECT LIST.. Providing alternate name for other will solve your issue.

CREATE VIEW KFF_Sales_Data_Updated
    AS
    SELECT CustSalesUpdated.cAuditNumber
       ,CustSalesUpdated.Account
       ,CustSalesUpdated.cAuditNumber as cAuditNumber_2
       ,CustSalesUpdated.Name
       ,StkSalesUpdated.cAuditNumber as AuditNumber1
       ,StkSalesUpdated.Code
       ,StkSalesUpdated.Credit
       ,StkSalesUpdated.Debit
       ,StkSalesUpdated.Description_1
       ,StkSalesUpdated.Id
       ,StkSalesUpdated.ItemGroup
       ,StkSalesUpdated.Quantity
       ,StkSalesUpdated.Reference
       ,StkSalesUpdated.TxDate
    FROM CustSalesUpdated
    INNER JOIN StkSalesUpdated
    ON StkSalesUpdated.cAuditNumber = CustSalesUpdated.cAuditNumber