CODEWITHSUNDEEP
sql-servertemp-tables
HumbleWebDev
HumbleWebDev

Reputation: 565

Cannot Select a Temporary table's Column?

Seems I cannot select the temporary table's column by the column name it inherits from the views I'm selecting from. I get the error: "Msg 207, Level 16, State 1, Procedure GetConsolidatedData, Line 44 [Batch Start Line 0] Invalid column name 'comp_id'."

select * 
into #tmp0
from Competitor_View
where [Competitor Name] like @competitor_name;

select * 
into #tmp1
from #tmp0
where [Competitor Part Number] like @competitor_part;

select * 
into #CustomerResult
from #tmp1
where [Koyo Part Number] like @koyo_part;

--------------

select * 
into #tmp3
from Customer_View
where [Customer Name] like @customer_name;

select * 
into #tmp4
from #tmp3
where [Customer Part Number] like @customer_part;

select * 
into #CompetitorResult
from #tmp4
where [Koyo Part Number] like @koyo_part;

--everything after this fails
select 
#CompetitorResult.comp_id,
#CompetitorResult.[Competitor Part Number], 
#CompetitorResult.[Competitor Comment], 
#CompetitorResult.[Competitor Name], 
#CompetitorResult.[Koyo Part Number] as [Associated Koyo Part Number],
#CustomerResult.cust_id, 
#CustomerResult.[Customer Part Number], 
#CustomerResult.[Customer Comment], 
#CustomerResult.[Customer Name] 
from #CompetitorResult
join #CustomerResult
on #CompetitorResult.[Koyo Part Number] = #CustomerResult.[Koyo Part Number];

I know I've got the right name for the column because running the following confirms that:

SELECT  *
FROM    tempdb.sys.columns 
WHERE object_id = OBJECT_ID('tempdb..#CompetitorResult')

Upvotes: 0

Views: 540

Answers (1)

M.Ali
M.Ali

Reputation: 69494

Your Query should look something like this... (without any temp tables)

select  cp.comp_id
       ,cp.[Competitor Part Number]
       ,cp.[Competitor Comment]
       ,cp.[Competitor Name]
       ,cp.[Koyo Part Number] as [Associated Koyo Part Number]
       ,cu.cust_id
       ,cu.[Customer Part Number]
       ,cu.[Customer Comment]
       ,cu.[Customer Name] 
from Competitor_View cp
INNER JOIN Customer_View cu ON cp.[Koyo Part Number] = cu.[Koyo Part Number]
where cp.[Koyo Part Number] like @koyo_part
  AND cp.[Competitor Part Number] like @competitor_part
  AND cp.[Competitor Name] like @competitor_name
  AND cu.[Customer Name] like @customer_name
  AND cu.[Customer Part Number] like @customer_part
  AND cu.[Koyo Part Number] like @koyo_part

Upvotes: 2

Related Questions