Solved

Why can't I JOIN to a @TempTable ?

Posted on 2008-10-13
6
1,011 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 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Addition to SQL for dynamic fields 6 38
Need some help to cast ntext to nvarchar SQL 2000 7 34
Query Syntax 17 36
Connection to multiple databases 13 19
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

777 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