Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Why can't I JOIN to a @TempTable ?

Posted on 2008-10-13
6
Medium Priority
?
1,020 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
  • 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 1600 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 400 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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

971 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