Solved

Exposed names error

Posted on 2009-06-30
1
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 60

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

691 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