Solved

Exposed names error

Posted on 2009-06-30
1
235 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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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