Sql table variable not existing after creation?

Question

I'm running the following query, but getting the error saying that my table variables don't exist in the last query. Can anyone see why this is happening and how to correct?

DECLARE @customers Table(custName varchar(20), custCode varchar(10) )

INSERT INTO @customers
select distinct  JM.CustLongName, JM.CustomerCode
    From Jobmaster JM
    WHERE StartDate > '20091031' 

DECLARE @Oct Table(custCode varchar(10), PromiseAvg decimal(10,4), ShipAvg decimal(10,4) )

--The following statement is run 12 times with the different months of the year
INSERT INTO @Oct
SELECT  JM.CustomerCode
  , isnull(AVG(Cast(DATEDIFF(dd, ISNULL(startDate, 0), ISNULL(PromiseDate, 0)) As Decimal)),0) As PromiseAvg
  , isnull(AVG(Cast(DATEDIFF(dd, ISNULL(startDate, 0), ISNULL(BOL.ShipDate, 0)) As Decimal)),0) As ShipAvg
from jobitems JI
LEFT JOIN jobmaster JM ON JI.Job_Number = JM.JobNumber
LEFT JOIN dbo.NCL_BOLDetails BD ON JM.JobNumber = BD.JobNumber AND JI.Item_Code = BD.ItemNumber
INNER JOIN dbo.NCL_BOLs BOL ON BD.BOLID = BOL.BOLID
WHERE StartDate BETWEEN '20101001' AND '20101031'    
Group By JM.CustomerCode, JM.CustLongName
Order By JM.CustomerCode

/*....copies removed for brevity.....*/

Select [@customers].custCode, [@customers].custName
  , [@Nov].PromiseAvg, [@Nov].ShipDate
  , [@Dec].PromiseAvg, [@Dec].ShipDate
  , [@Jan].PromiseAvg, [@Jan].ShipDate
  , [@Feb].PromiseAvg, [@Feb].ShipDate
  , [@Mar].PromiseAvg, [@Mar].ShipDate
  , [@Apr].PromiseAvg, [@Apr].ShipDate
  , [@May].PromiseAvg, [@May].ShipDate
  , [@Jun].PromiseAvg, [@Jun].ShipDate
  , [@Jul].PromiseAvg, [@Jul].ShipDate
  , [@Aug].PromiseAvg, [@Aug].ShipDate
  , [@Sep].PromiseAvg, [@Sep].ShipDate
  , [@Oct].PromiseAvg, [@Oct].ShipDate
FROM @customers
LEFT JOIN [@Nov] ON  [@customers].custCode = [@Nov].custCode
LEFT JOIN [@Dec] ON  [@customers].custCode = [@Dec].custCode
LEFT JOIN [@Jan] ON  [@customers].custCode = [@Jan].custCode
LEFT JOIN [@Feb] ON  [@customers].custCode = [@Feb].custCode
LEFT JOIN [@Mar] ON  [@customers].custCode = [@Mar].custCode
LEFT JOIN [@Apr] ON  [@customers].custCode = [@Apr].custCode
LEFT JOIN [@May] ON  [@customers].custCode = [@May].custCode
LEFT JOIN [@Jun] ON  [@customers].custCode = [@Jun].custCode
LEFT JOIN [@Jul] ON  [@customers].custCode = [@Jul].custCode
LEFT JOIN [@Aug] ON  [@customers].custCode = [@Aug].custCode
LEFT JOIN [@Sep] ON  [@customers].custCode = [@Sep].custCode
LEFT JOIN [@Oct] ON  [@customers].custCode = [@Oct].custCode

Are you running all of this at once or parts of it at a time? — Mark SQLDev
– Mark SQLDev, Commented Dec 6, 2010 at 18:55

amantur · Accepted Answer · 2010-12-06 18:59:34Z

2

try something like this:

Select c.custCode, c.custName
  , n.PromiseAvg, n.ShipDate
  , d.PromiseAvg, d.ShipDate
  , j.PromiseAvg, j.ShipDate
  , f.PromiseAvg, f.ShipDate
  , m.PromiseAvg, m.ShipDate
  , a.PromiseAvg, a.ShipDate
  , mm.PromiseAvg, mm.ShipDate
  , jj.PromiseAvg, jj.ShipDate
  , jl.PromiseAvg, jl.ShipDate
  , ag.PromiseAvg, ag.ShipDate
  , s.PromiseAvg, s.ShipDate
  , o.PromiseAvg, o.ShipDate
FROM @customers c
LEFT JOIN @Nov n ON  c.custCode = n.custCode
LEFT JOIN @Dec d ON  c.custCode = d.custCode
LEFT JOIN @Jan j ON  c.custCode = j.custCode
LEFT JOIN @Feb f ON  c.custCode = f.custCode
LEFT JOIN @Mar m ON  c.custCode = m.custCode
LEFT JOIN @Apr a ON  c.custCode = a.custCode
LEFT JOIN @May mm ON  c.custCode = mm.custCode
LEFT JOIN @Jun jj ON  c.custCode = jj.custCode
LEFT JOIN @Jul jl ON  c.custCode = jl.custCode
LEFT JOIN @Aug ag ON  c.custCode = ag.custCode
LEFT JOIN @Sep s ON  c.custCode = s.custCode
LEFT JOIN @Oct o ON  c.custCode = o.custCode

answered Dec 6, 2010 at 18:59

amantur

40.6k22 gold badges136 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MAW74656 Over a year ago

Although I'm a bit uncertain about taking advice from a VilliageIdiot, he seems to have become the VillageGenius! I changed ShipDate to ShipAvg, as that is the column I needed and it worked!

amantur Over a year ago

I have noted this, but as you commented that /*....copies removed for brevity.....*/ I thought may be this exists and is not shown in question.

MAW74656 Over a year ago

The section before that comment is repeated with the only change being the 3 letter month abbreviation. Its exactly the same otherwise. Copy and paste, then find/replace.

Martin Smith · Accepted Answer · 2010-12-06 18:52:06Z

1

You have missed out the alias

FROM @customers

should be

FROM @customers [@customers]

You will probably need to change LEFT JOIN [@Nov] to LEFT JOIN @Nov [@Nov] etc. as well unless these are actually permanent tables. So your query needs to look like

...
FROM @customers [@customers]
LEFT JOIN @Nov [@Nov] ON  [@customers].custCode = [@Nov].custCode
LEFT JOIN @Dec [@Dec] ON  [@customers].custCode = [@Dec].custCode
LEFT JOIN @Jan [@Jan] ON  [@customers].custCode = [@Jan].custCode
...

answered Dec 6, 2010 at 18:52

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

9 Comments

Martin Smith Over a year ago

@Maw74656 - You'll need to do it with all the tables in the join as well.

MAW74656 Over a year ago

Are you saying that i HAVE to use alias's to reference table variables?

Martin Smith Over a year ago

@MAW74656 Yes. When you table qualify columns from a TV you always need to alias the TV. Bit of a pain!

MAW74656 Over a year ago

I have this changed now, but I'm getting a Line 188: Incorrect syntax near '.'

Mike Cheel Over a year ago

I have never had to alias my table variables.

|

Randy Minder · Accepted Answer · 2010-12-06 18:46:12Z

0

If I had to guess, I would say you have a GO statement in there somewhere that we cannot see. If so, this would cause all declared variables occurring before to lose scope, and be unavailable after the GO. A semi-colon might have the same effect, but I'm not confident of that.

answered Dec 6, 2010 at 18:46

Randy Minder

48.8k55 gold badges222 silver badges373 bronze badges

2 Comments

Randy Minder Over a year ago

@MAW74656 - Ok. One last suggestion. Remove the brackets around @customers in your Select statement.

amantur Over a year ago

using GO between statements will render all the variables declared before GO inaccessible after it.

bernd_k · Accepted Answer · 2010-12-06 19:18:45Z

0

I don't find the column Shipdate defined. A solution might look similar too

...
Select c.custCode, c.custName
  , nov.PromiseAvg, nov.ShipDate
  , dec.PromiseAvg, dec.ShipDate
FROM @customers c
LEFT JOIN @Nov nov ON  c.custCode = nov.custCode
LEFT JOIN @Dec dec ON  c.custCode = dec.custCode

answered Dec 6, 2010 at 19:18

bernd_k

12.1k7 gold badges49 silver badges64 bronze badges

Collectives™ on Stack Overflow

Sql table variable not existing after creation?

4 Answers 4

3 Comments

9 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

9 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related