Solved

Exposed names error

Posted on 2009-06-30
1
236 Views
Last Modified: 2012-05-07
I am receiving the following error from the attached query, can someone help me understand where I am wrong with the query.

Msg 1013, Level 16, State 1, Line 126
The objects "#TempTable_PLC_GLOBAL_PA_AD" and "#TempTable_PLC_GLOBAL_PA_AD" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

DROP TABLE #tempTable_PLC_GLOBAL_UNIQUE

DROP TABLE #tempTable_PLC_GLOBAL_PA

IF OBJECT_ID ('#tempTable_PLC_GLOBAL_UNIQUE') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_UNIQUE

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_CX') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_CX

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_DO') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_DO

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_ED') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_ED

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_EM') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_EM

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_ES') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_ES

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_GA') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_GA

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_NE') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_NE

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_PA') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_PA

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_PE') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_PE

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_SA') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_SA

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_VR') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_VR

IF OBJECT_ID ('tempdb..#tempTable_PLC_GLOBAL_PA_AD') IS NOT NULL DROP TABLE #tempTable_PLC_GLOBAL_PA_AD

IF OBJECT_ID ('tempdb..#tempTable_PLC_USER') IS NOT NULL DROP TABLE #tempTable_PLC_USER
 
 

/**** Get Global Unique ****/
 

SELECT     dbo.PLC_GLOBAL_UNIQUE_1.[Material Number], pim89.dbo.pmg.[Material Group], pim89.dbo.pmg.Division, 
 

pim89.dbo.[PL1-GBU].[Group Name], pim89.dbo.[PL1-GBU].[Org Name], 

                      pim89.dbo.[PL1-GBU].[Hp Business Area Hierarchy]

INTO #tempTable_PLC_GLOBAL_UNIQUE                      

                      

FROM         dbo.PLC_GLOBAL_UNIQUE_1 LEFT OUTER JOIN

                      pim89.dbo.pmg ON dbo.PLC_GLOBAL_UNIQUE_1.[Material Number] = pim89.dbo.pmg.[Material Number] LEFT 
 

OUTER JOIN

                      pim89.dbo.[PL1-GBU] ON pim89.dbo.pmg.Division = pim89.dbo.[PL1-GBU].[Product Line]
 

/**** Get Global CX ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_CX                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'CX')
 

/**** Get Global VR ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_VR                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'VR')
 

/**** Get Global DO ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_DO                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'DO')
 

/**** Get Global ED ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_ED                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'ED')
 

/**** Get Global EM ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_EM                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'EM')
 

/**** Get Global ES ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_ES                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'ES')
 

/**** Get Global GA ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_GA                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'GA')
 

/**** Get Global NE ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_NE                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'NE')
 

/**** Get Global PA ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_PA                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'PA')
 

/**** Get Global PE ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_PE                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'PE')
 

/**** Get Global SA ****/
 

SELECT     [Material Number], [Global Event], [Planned Date]

INTO #tempTable_PLC_GLOBAL_SA                      

FROM         dbo.PLC_Global

WHERE     ([Global Event] = N'SA')
 
 

/**** Get Global PA-AD ****/
 

SELECT     [Material Number], [Planned Date]

INTO #tempTable_PLC_GLOBAL_PA_AD                      

FROM         dbo.ZWPLCSBEV

WHERE     ([Event Table] = 'GE') AND ([Sub Event Code] = 'AD')
 

/**** Get Changed By ****/
 

SELECT     [Material Number], MAX([Change User]) AS ChangedBy 

INTO #tempTable_PLC_USER

FROM         dbo.ZWPLCGBEV

GROUP BY [Material Number]
 
 

/**** Get Global INFORMATION ****/
 

SELECT     dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number], dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Group],dbo.#TempTable_PLC_GLOBAL_UNIQUE.Division, 

                      dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Group Name], dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Org Name], 

                      dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Hp Business Area Hierarchy], 
 

dbo.#TempTable_PLC_GLOBAL_VR.[Planned Date] AS VR_Planned, dbo.#TempTable_PLC_GLOBAL_DO.[Planned Date] AS DO_Planned, 

                      dbo.#TempTable_PLC_GLOBAL_CX.[Planned Date] AS CX_Planned, 

                      dbo.#TempTable_PLC_GLOBAL_NE.[Planned Date] AS NE_Planned, 

                      dbo.#TempTable_PLC_GLOBAL_PA.[Planned Date] AS PA_Planned,

                      dbo.#TempTable_PLC_GLOBAL_GA.[Planned Date] AS GA_Planned,

                      dbo.#TempTable_PLC_GLOBAL_SA.[Planned Date] AS SA_Planned,

                      dbo.#TempTable_PLC_GLOBAL_PE.[Planned Date] AS PE_Planned,

                      dbo.#TempTable_PLC_GLOBAL_ES.[Planned Date] AS ES_Planned,

                      dbo.#TempTable_PLC_GLOBAL_EM.[Planned Date] AS EM_Planned,

                      dbo.#TempTable_PLC_GLOBAL_ED.[Planned Date] AS ED_Planned,

                      dbo.#TempTable_PLC_GLOBAL_PA_AD.[Planned Date] AS PA_AD_Planned,

                      dbo.#tempTable_PLC_USER.ChangedBy

INTO PLC_GLOBAL_INFO                      

                      

FROM         dbo.#TempTable_PLC_GLOBAL_UNIQUE LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_ED ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_ED.[Material Number] LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_NE ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_NE.[Material Number] LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_DO ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_DO.[Material Number] LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_CX ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_CX.[Material Number] LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_ES ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_ES.[Material Number] LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_EM ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_EM.[Material Number] LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_PE ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_PE.[Material Number] LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_PA ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_PA.[Material Number] LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_GA ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_GA.[Material Number] LEFT OUTER JOIN

                      dbo.#TempTable_PLC_GLOBAL_SA ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_SA.[Material Number] LEFT OUTER JOIN

		      dbo.#TempTable_PLC_GLOBAL_VR ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_VR.[Material Number]LEFT OUTER JOIN

		      dbo.#TempTable_PLC_GLOBAL_PA_AD ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_PA_AD.[Material Number]LEFT OUTER JOIN

		      dbo.#TempTable_PLC_GLOBAL_PA_AD ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_GLOBAL_PA_AD.[Material Number]LEFT OUTER JOIN

		      dbo.#TempTable_PLC_USER ON dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = 
 

dbo.#TempTable_PLC_USER.[Material Number]

Open in new window

0
Comment
Question by:Fairfield
1 Comment
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24746310
You have a duplicate JOIN statement in for that table:

LEFT OUTER JOIN dbo.#TempTable_PLC_GLOBAL_PA_AD ON      dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = dbo.#TempTable_PLC_GLOBAL_PA_AD.[Material Number]
LEFT OUTER JOIN dbo.#TempTable_PLC_GLOBAL_PA_AD ON      dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = dbo.#TempTable_PLC_GLOBAL_PA_AD.[Material Number]

They appear to be identical, so probably just a cut and paste error.  If that is the case, then just delete one of the join expressions.  If you meant for these to be two different joins then optionally adjust the join criteria to get two different resultsets from that table AND (this is the important part) use aliases to reference them so SQL can tell them apart.

e.g.

LEFT OUTER JOIN dbo.#TempTable_PLC_GLOBAL_PA_AD paAD1 ON      dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = paAD1.[Material Number]
LEFT OUTER JOIN dbo.#TempTable_PLC_GLOBAL_PA_AD paAD2 ON      dbo.#TempTable_PLC_GLOBAL_UNIQUE.[Material Number] = paAD2.[Material Number]
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 43
Columnstore Indexes - real-time operational analytics 1 13
TSQL previous 5 25
Sql Query Datatype 2 19
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now