SQL query column name specified multiple times error

[former_member268870]

Experts,

I'm trying to get JE line detail via an SQL query from 2 databases, and need to show the account segment names for Division and Region. My issue lies that in my query these fields are T3.[Name] and T4.[Name]. When I combine these at the end of my query as DataT_1.[Name] it is giving me this error.

I simplified the Query to solve this issue...

I assume it is because it cannot distinguish between the 2 [Name] fields anymore.....

Your insights would be most welcome.

WITH DataT AS 
	(SELECT   'ST' AS Company, 
	T2.[TransID],
	T2.RefDate,
	T0.FormatCode,
	T3.[Name],
	T4.[Name]


	 FROM      Inguran.dbo.OACT T0 INNER JOIN
                               Inguran.dbo.JDT1 T1 ON T0.AcctCode = T1.Account INNER JOIN
                               Inguran.dbo.OJDT T2 ON T1.TransId = T2.TransId RIGHT OUTER JOIN 
							   Inguran.dbo.OASC T3 ON T0.Segment_1 = T3.Code RIGHT OUTER JOIN 
							   Inguran.dbo.OASC T4 ON T0.Segment_2 = T4.Code  
							   
                     WHERE   (T1.Debit <> T1.Credit) 
				     AND T2.RefDate Between '01-01-2018' AND '01-10-2018'
					 AND (T3.SegmentId=1)
					 AND (T4.SegmentId=2)
					
                     GROUP BY 
					 T2.[TransID],T2.RefDate,T0.FormatCode,T3.[Name],T4.[Name]                  
UNION ALL


SELECT   'STCAN' AS Company, 
	T2.[TransID],
	T2.RefDate,
	T0.FormatCode,
	T3.[Name],
	T4.[Name]


	 FROM      ST_Canada.dbo.OACT T0 INNER JOIN
                               ST_Canada.dbo.JDT1 T1 ON T0.AcctCode = T1.Account INNER JOIN
                               ST_Canada.dbo.OJDT T2 ON T1.TransId = T2.TransId RIGHT OUTER JOIN 
							   ST_Canada.dbo.OASC T3 ON T0.Segment_1 = T3.Code RIGHT OUTER JOIN 
							   ST_Canada.dbo.OASC T4 ON T0.Segment_2 = T4.Code  
							   
                     WHERE   (T1.Debit <> T1.Credit) 
				     AND T2.RefDate Between '01-01-2018' AND '01-10-2018'
					 AND (T3.SegmentId=1)
					 AND (T4.SegmentId=2)
					 
					 
                     GROUP BY 
					 T2.[TransID],T2.RefDate,T0.FormatCode,T3.[Name],T4.[Name]     
),					




Comp AS
    
(SELECT   'ST' AS Company
    UNION ALL
 SELECT   
 'STCAN' AS Company)






SELECT   Comp_1.Company,  
			DataT_1.[TransID],
			DataT_1.RefDate,
			(Year (DataT_1.RefDate)) AS 'Posting Year',
			(Month (DataT_1.RefDate)) AS 'Posting Month',
			DataT_1.FormatCode,
			DataT_1.[Name]
   FROM     Comp AS Comp_1 LEFT OUTER JOIN
                DataT AS DataT_1 ON Comp_1.Company = DataT_1.Company

[former_member390407 · Accepted solution]

Most likely the problem is that you use same names for 2 columns in your CTE. Try to set aliases for your columns like:

WITH DataT (Your aliases) AS (...)

And then in your select query them by aliases

This works for me:

WITH DataT (Company, TransId, RefDate, FormatCode, Name1, Name2) AS 
	(SELECT   'ST' AS Company, 
	T2.[TransID],
	T2.RefDate,
	T0.FormatCode,
	T3.[Name],
	T4.[Name]




	 FROM      SBODemoGB.dbo.OACT T0 INNER JOIN
                               SBODemoGB.dbo.JDT1 T1 ON T0.AcctCode = T1.Account INNER JOIN
                               SBODemoGB.dbo.OJDT T2 ON T1.TransId = T2.TransId RIGHT OUTER JOIN 
							   SBODemoGB.dbo.OASC T3 ON T0.Segment_1 = T3.Code RIGHT OUTER JOIN 
							   SBODemoGB.dbo.OASC T4 ON T0.Segment_2 = T4.Code  
							   
                     WHERE   (T1.Debit <> T1.Credit) 
				     AND T2.RefDate Between '01-01-2018' AND '01-10-2018'
					 AND (T3.SegmentId=1)
					 AND (T4.SegmentId=2)
					
                     GROUP BY 
					 T2.[TransID],T2.RefDate,T0.FormatCode,T3.[Name],T4.[Name]                  
UNION ALL




SELECT   'STCAN' AS Company, 
	T2.[TransID],
	T2.RefDate,
	T0.FormatCode,
	T3.[Name],
	T4.[Name]




	 FROM      SBODemoUS.dbo.OACT T0 INNER JOIN
                               SBODemoUS.dbo.JDT1 T1 ON T0.AcctCode = T1.Account INNER JOIN
                               SBODemoUS.dbo.OJDT T2 ON T1.TransId = T2.TransId RIGHT OUTER JOIN 
							   SBODemoUS.dbo.OASC T3 ON T0.Segment_1 = T3.Code RIGHT OUTER JOIN 
							   SBODemoUS.dbo.OASC T4 ON T0.Segment_2 = T4.Code  
							   
                     WHERE   (T1.Debit <> T1.Credit) 
				     AND T2.RefDate Between '01-01-2018' AND '01-10-2018'
					 AND (T3.SegmentId=1)
					 AND (T4.SegmentId=2)
					 
					 
                     GROUP BY 
					 T2.[TransID],T2.RefDate,T0.FormatCode,T3.[Name],T4.[Name]     
),					








Comp AS
    
(SELECT   'ST' AS Company
    UNION ALL
 SELECT   
 'STCAN' AS Company)












SELECT   Comp_1.Company,  
			DataT_1.[TransID],
			DataT_1.RefDate,
			(Year (DataT_1.RefDate)) AS 'Posting Year',
			(Month (DataT_1.RefDate)) AS 'Posting Month',
			DataT_1.FormatCode,
			DataT_1.Name1
   FROM     Comp AS Comp_1 LEFT OUTER JOIN
                DataT AS DataT_1 ON Comp_1.Company = DataT_1.Company