Skip to Content

SQL query column name specified multiple times error

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Jul 18, 2018 at 02:05 PM

    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
    
    Add comment
    10|10000 characters needed characters exceeded