Solved

Must declare memory variable @Map

Posted on 2014-09-16
4
183 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
[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
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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
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

627 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