Solved

Must declare memory variable @Map

Posted on 2014-09-16
4
173 Views
Last Modified: 2014-09-16
I'm using a memory table as a bridge between multiple versions of the same table, so that I can look at value in successive (by date/time) records in the table.  I've declared the variable, inserted into it, and when I do a select statement on that table it works:
DECLARE @Map TABLE (Equip_ID int, docDate datetime, Prod_ID smallint, NextEntry datetime);

INSERT INTO @Map(Equip_ID, docDate, Prod_ID, NextEntry)
SELECT TR1.Equip_ID, TR1.docDate, TR1.Prod_ID, MIN(TR2.docDate) as NextEntry
FROM tbl_Readings_Tanks as TR1
LEFT JOIN tbl_Readings_Tanks as TR2
ON TR1.Equip_ID = TR2.Equip_ID
AND TR1.Prod_ID = TR2.Prod_ID
AND TR1.docDate < TR2.docDate
WHERE ((@StartDate IS NULL) OR (TR1.docDate >= @StartDate))
AND ((@EndDate IS NULL) OR (TR1.Docdate < dateadd(day, 1, @EndDate)))
GROUP BY TR1.Equip_ID, TR1.docDate, TR1.Prod_ID

SELECT * FROM @Map

Open in new window


But if I replace the last SELECT statement with:
SELECT TR1.TankReadingID
, TR1.Equip_ID 
, TR1.Inches_Start
, TR1.Inches_End
, isnull(TR2.Inches_Start, TR2.Inches_End) as NextStart
, TR2.Inches_Start
, TR2.Inches_End
, TR2.Increase 
FROM tbl_Readings_Tanks as TR1
INNER JOIN @Map ON TR1.Equip_ID = @Map.Equip_ID AND TR1.docDate = @Map.docDate AND TR1.Prod_ID = @Map.Prod_ID

Open in new window

I get the error:
msg 137, Level 16, State 1, Line 28  
Must declare the scalar variable @Map

Note: line 28 is the INNER JOIN line

Interestingly, when I alias @Map in that line:
INNER JOIN @Map as M ON TR1.Equip_ID = M.Equip_ID AND TR1.docDate = M.docDate AND TR1.Prod_ID = M.Prod_ID

Open in new window

it works.  Can anyone please explain that to me
0
Comment
Question by:Dale Fye (Access MVP)
[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
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40326922
As you have discovered, you need to alias the variable of type table if you are joining against another table.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 40326926
This is from SQL Server's BOL table (Transact-SQL):
table variables can be referenced by name in the FROM clause of a batch, as shown the following example:

View ColorizedCopy to ClipboardPrintSELECT Employee_ID, Department_ID FROM @MyTableVar;
 
SELECT Employee_ID, Department_ID FROM @MyTableVar;

Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

View ColorizedCopy to ClipboardPrintSELECT EmployeeID, DepartmentID
  FROM @MyTableVar m
  JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
     m.DepartmentID = Employee.DepartmentID);
0
 
LVL 48

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40326962
Thanks, AC.

I had the post fully written, then thought, let my try and alias it.  Don't know why I thought of that, or why they would construct this object so that it requires an alias within a join.  

Oh well, just the way it's done!

I'm enjoying this migration to SQL Server but I'm afraid I'm just barely scratching the surface with all of the techniques I've learned in the last week or so.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40327021
Don't know why I thought of that, or why they would construct this object so that it requires an alias within a join.  
Then you may want to consider why the word "enough" is spelled the way it is in English or why in the US you drive on the parkway and park on the driveway.  

But I digress take the following (silly) query:
SELECT  *
FROM    (SELECT *
            FROM TableName)

it fails with the error:
Incorrect syntax near ')'.

And requires an alias as in:
SELECT  *
FROM    (SELECT *
            FROM TableName) a
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

752 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