Solved

Must declare memory variable @Map

Posted on 2014-09-16
4
167 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)
  • 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 47

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

713 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