5743

SQL The column 'XXX' was specified multiple times for 'YYY'

Question:

I am having a hard time on this one. Let me tell you that I am a beginner with SQL.

I found many threads with the same error but none that I could use. I want to use a calculated column in a WHERE cause, so from this, that runs:

SELECT ST.TransSerial , ST.TransDocument , ST.TransDocNumber , ST.CreateDate , ST.DeferredPaymentDate , ST.PartyID , ST.PartyName , ST.PartyAddress1 , ST.PartyAddress2 , ST.PartyPostalCode , ST.PartyFederalTaxID , ST.LineItemsCount , ST.TotalNetAmount , ST.TotalTaxAmount , ST.TotalAmount , CLA.TransSerial , CLA.TransDocument , CLA.TransDocNumber , CLA.TotalAmount , CLA.TotalPendingAmount , CLA.TotalAmount - CLA.TotalPendingAmount AS 'TotalPaidAmount' , ST.TransDocument + '/' + CAST( ST.TransDocNumber AS VARCHAR(100)) AS 'Document' , CASE WHEN CLA.DeferredPaymentDate < CONVERT( DATE, GETDATE()) THEN 'Deferred' ELSE 'Not Deferred' END AS 'Deferred' , CASE CLA.TotalPendingAmount WHEN 0 THEN 'Paid' ELSE 'Not Paid' END AS 'Paid' FROM [513745653Data].dbo.SaleTransaction AS ST LEFT JOIN [513745653Data].dbo.CustomerLedgerAccount AS CLA ON ST.TransSerial = CLA.TransSerial AND ST.TransDocument = CLA.TransDocument AND ST.TransDocNumber = CLA.TransDocNumber

I went to this, that doesn't run:

SELECT AAA.* FROM ( SELECT ST.TransSerial , ST.TransDocument , ST.TransDocNumber , ST.CreateDate , ST.DeferredPaymentDate , ST.PartyID , ST.PartyName , ST.PartyAddress1 , ST.PartyAddress2 , ST.PartyPostalCode , ST.PartyFederalTaxID , ST.LineItemsCount , ST.TotalNetAmount , ST.TotalTaxAmount , ST.TotalAmount , CLA.TransSerial , CLA.TransDocument , CLA.TransDocNumber , CLA.TotalAmount , CLA.TotalPendingAmount , CLA.TotalAmount - CLA.TotalPendingAmount AS 'TotalPaidAmount' , ST.TransDocument + '/' + CAST( ST.TransDocNumber AS VARCHAR(100)) AS 'Document' , CASE WHEN CLA.DeferredPaymentDate < CONVERT( DATE, GETDATE()) THEN 'Deferred' ELSE 'Not Deferred' END AS 'Deferred' , CASE CLA.TotalPendingAmount WHEN 0 THEN 'Paid' ELSE 'Not Paid' END AS 'Paid' FROM [513745653Data].dbo.SaleTransaction AS ST LEFT JOIN [513745653Data].dbo.CustomerLedgerAccount AS CLA ON ST.TransSerial = CLA.TransSerial AND ST.TransDocument = CLA.TransDocument AND ST.TransDocNumber = CLA.TransDocNumber ) AS AAA

And I get this error:

SQL Error [8156] [S0001]: The column 'TransSerial' was specified multiple times for 'AAA'.

I can't apply what I see on other threads about this, what am I missing here?

Thanks in advance.

Answer1:

Inside sub-queries, you cannot have duplicate column names. Just alias the duplicate column name(s):

SELECT AAA.* FROM ( SELECT ST.TransSerial , ST.TransDocument , ST.TransDocNumber , ST.CreateDate , ST.DeferredPaymentDate , ST.PartyID , ST.PartyName , ST.PartyAddress1 , ST.PartyAddress2 , ST.PartyPostalCode , ST.PartyFederalTaxID , ST.LineItemsCount , ST.TotalNetAmount , ST.TotalTaxAmount , ST.TotalAmount , CLA.TransSerial AS CLA_TransSerial, -- alias CLA.TransDocument AS CLA_TransDocument, CLA.TransDocNumber AS CLA_TransDocNumber, CLA.TotalAmount AS CLA_TotalAmount, CLA.TotalPendingAmount , CLA.TotalAmount - CLA.TotalPendingAmount AS 'TotalPaidAmount' , ST.TransDocument + '/' + CAST( ST.TransDocNumber AS VARCHAR(100)) AS 'Document' , CASE WHEN CLA.DeferredPaymentDate < CONVERT( DATE, GETDATE()) THEN 'Deferred' ELSE 'Not Deferred' END AS 'Deferred' , CASE CLA.TotalPendingAmount WHEN 0 THEN 'Paid' ELSE 'Not Paid' END AS 'Paid' FROM [513745653Data].dbo.SaleTransaction AS ST LEFT JOIN [513745653Data].dbo.CustomerLedgerAccount AS CLA ON ST.TransSerial = CLA.TransSerial AND ST.TransDocument = CLA.TransDocument AND ST.TransDocNumber = CLA.TransDocNumber ) AS AAA

Recommend