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
Solved

Must declare memory variable @Map

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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

856 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