Solved

Exposed names error

Posted on 2009-06-30
1
238 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 with cast 38 54
Master DB with Masterkey 1 34
TSQL recursive CTE challenge... 8 29
SQL - Curser to do an insert based on a select 2 12
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

831 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