Solved

Why can't I JOIN to a @TempTable ?

Posted on 2008-10-13
6
1,010 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now