Solved

Why can't I JOIN to a @TempTable ?

Posted on 2008-10-13
6
1,016 Views
Last Modified: 2012-05-05
I have written the following T-SQL, which works correctly up to the last line.  I'm using SQL Server 2000.

After filling the @TempTable I can't join it to a "real" table.  The error message returned is "Must declare the variable '@TempTable'."

What really confuses me is that if I modify the last line to read "SELECT * FROM @TempTable" it works without problem.  Seems like the join is the problem.

Thanks.


DECLARE @PN varchar(15), 
@Hours Varchar(15)
 
DECLARE @TempTable TABLE(ItemNumber Varchar(15), Hours Varchar(15))
 
DECLARE  CursorTemplate CURSOR 
FAST_FORWARD FOR       
      SELECT DISTINCT Item FROM Tedia.dbo.FSER_Invoice 
 
OPEN CursorTemplate
 
FETCH NEXT FROM CursorTemplate 
INTO      @Pn
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
	
    INSERT INTO @TempTable
 
    SELECT DISTINCT
	TopLevelItemNumber, Sum(RequiredQuantity / ParentItemLotSizeQuantity) AS Labor
 
	FROM FSDBB1.dbo.FSE_MultiLevelBill(@PN)
 
	WHERE ComponentType = 'R'
 
	GROUP BY TopLevelItemNumber
	
 
    FETCH NEXT FROM CursorTemplate 
    INTO @PN
END
 
CLOSE CursorTemplate
DEALLOCATE CursorTemplate
 
--***** NEXT LINE IS PROBLEM *****
 
SELECT * FROM Tedia.dbo.FSER_Invoice JOIN @TempTable ON Tedia.dbo.FSER_Invoice.Item = @TempTable.ItemNumber

Open in new window

0
Comment
Question by:SteveB2175
[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
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:CCongdon
ID: 22702382
Are you sending an entire table across as a 'variable'?
Also, maybe I missed something, but when I've created temp tables, I've always prefaced them with # not @. And I used CREATE TABLE not DECLARE.
0
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 400 total points
ID: 22702412
u need to alias your variable table like this

SELECT
*
FROM Tedia.dbo.FSER_Invoice
JOIN @TempTable tt
      ON Tedia.dbo.FSER_Invoice.Item = tt.ItemNumber

and it should work
0
 
LVL 9

Assisted Solution

by:CCongdon
CCongdon earned 100 total points
ID: 22702422
OK, I see. I just looked it up. I understand now. You need to declare an alias for the table in the select statement. For example, this gets the same error you showed:
SELECT id
    FROM @foo
    INNER JOIN #foo
    ON @foo.id = #foo.id

 
But, this works:
SELECT id
    FROM @foo f
    INNER JOIN #foo
    ON f.id = #foo.id
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:Cvijo123
ID: 22702435

@CCongdon: when you use @ that means it is variable table (memory table) and when you used # that means table is saved on disk.

makeing @ tables you use Declare @tableName table statment and for # tables you use Create table #tableName
0
 

Author Comment

by:SteveB2175
ID: 22702506
That worked.  I never would have found it on my own.  I don't understand the underlying logic that requires an alias, but I'll go back and look for that.

Thanks for your help.
0
 
LVL 9

Expert Comment

by:CCongdon
ID: 22702547
@Cvijo123: Yeah, I learned something new today. I found a site the explained the difference between a declared table and a temp table (such as being able to use a declared table inside a function). The declared table would appear to be SUPER temporary and doesn't even hit the transaction log amongst other things.
 
@SteveB2175:
Here's the site I got my info from. It also shows some of the limitations of the table variable.
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
And the correct term for an @Table is a 'Table Variable' so I was sort of right :) A temp table is created with #....
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
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…

717 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