?
Solved

Must declare memory variable @Map

Posted on 2014-09-16
4
Medium Priority
?
215 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
  • 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 2000 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 49

Author Closing Comment

by:Dale Fye
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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 …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

592 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