Problem with a temp table

For some reason the compiler is telling me that I must declarethe
variable @.costcenter_tmp on lines 74 and 98...but if i put a select
statement in ther (for testing) before the loop I get data back from
the temp table..

why is this happening to this temp table and no others?..heres my
code..its a little lengthy

--Fiscal Year
declare @.year smallint
set @.year = 2004

--Month number the Fiscal year starts and ends
declare @.month smallint
set @.month = 7

--Place holder for number of costcenters
declare @.cccounter smallint

--loop counter for cost centers
declare @.ccount smallint
set @.ccount = 1

--Place holder for number of payor types
declare @.ptcounter smallint

--loop counter for payor types
declare @.pcount smallint
set @.pcount = 1

--Temp table to store the blank values for all cost centers/payor
types for the fiscal year
declare @.Recorded_Revenue_tmp table
(
Revenue money default 0,
[Date] varchar(15),
monthn smallint,
[Cost Center] varchar(50),
[Payor Type] varchar(50)
)

--Temp table to store the values of the coster centers
declare @.costcenter_tmp table
(
ccid int IDENTITY (1,1),
ccname varchar(50)
)

--Inserts cost centers and code into the @.costcenter_tmp temp table
insert into @.costcenter_tmp (ccname) select costcenter.fullname + ' '
+ costcenter.code from costcenter, agency_cost_center
where costcenter.oid = agency_cost_center.cost_center_moniker

--Sets the @.cccounter variable to the number of cost centers
select @.cccounter = count(*) from @.costcenter_tmp

--Temp table to store the values of the payor types
declare @.payor_type_tmp table
(
ptid int identity (1,1),
ptname varchar(50)
)

--Inserts payor types into the @.payor_type_tmp temp table
Insert into @.payor_type_tmp(ptname)select fullname from payor_type
where payor_type.oid = payor.payor_type_moniker

--Sets the @.ptcounter variable to the number of payor types
select @.ptcounter = count(*) from @.payor_type_tmp

--Loop that gets the first part of the fiscal year
While (@.month <13)
begin
--Loop that gets the value of the cost center to insert
While (@.ccount <= @.cccounter)
begin
--Loop that inserts values for the first part of the fiscal year into
the @.Recorded_Revenue_tmp temp table
while (@.pcount <= @.ptcounter)
begin
Insert into @.Recorded_Revenue_tmp(Revenue, [Date], monthn, [Cost
Center],[Payor Type])
select 0, datename(month, @.month)+ ' ' + @.year -1, @.month, [Cost
Center], [Payor Type]
from @.costcenter_tmp,@.payor_type_tmp where @.costcenter_tmp.ccid =
@.ccount and
@.payor_type_tmp.ptid = @.pcount
set @.pcount = @.pcount + 1
end
set @.pcount = 1
set @.ccount = @.ccount + 1
end
set @.ccount = 1
set @.month = @.month + 1
end

set @.month = 1

--Loop that inserts values for the second part of the fiscal year into
the @.Recorded_Revenue_tmp temp table
While (@.month <7)
begin
--Loop that gets the value of the cost center to insert
While (@.ccount <= @.cccounter)
begin
--Loop that inserts values for the first part of the fiscal year into
the @.Recorded_Revenue_tmp temp table
while (@.pcount <= @.ptcounter)
begin
Insert into @.Recorded_Revenue_tmp([Date], monthn, [Cost Center],[Payor
Type])
select 0,datename(month, @.month)+ ' ' + @.year, @.month, [Cost Center],
[Payor Type]
from @.costcenter_tmp, @.payor_type_tmp where @.costcenter_tmp.ccid =
@.ccount and
@.payor_type_tmp.ptid = @.pcount
set @.pcount = @.pcount + 1
end
set @.pcount = 1
set @.ccount = @.ccount + 1
end
set @.ccount = 1
set @.month = @.month + 1
end

--Pulls in all the data for the report
(select Revenue,[Date],[Cost Center],[Payor Type] from
@.Recorded_Revenue_tmp)

union

(select (revenue) as Revenue, (b.monthname + ' ' + Cast(b.yearn as
varchar(4))) as 'Date',
c.fullname + ' ' + c.code as 'Cost Center',d.fullname as 'Payor
Type'

from chr_recorded_revenue a, chr_recorded_revenue_dates b,
costcenter c, payor_type d

where a.date = b.day and a.[Cost Center]= c.oid and a.[Payor Type]
= d.oid)

order by d.fullname,b.monthn, c.oid

thanks..Jim[posted and mailed, please reply in news]

Jim (jim.ferris@.motorola.com) writes:
> For some reason the compiler is telling me that I must declarethe
> variable @.costcenter_tmp on lines 74 and 98...but if i put a select
> statement in ther (for testing) before the loop I get data back from
> the temp table..
> why is this happening to this temp table and no others?..heres my
> code..its a little lengthy
>...

The problem is here:

> from @.costcenter_tmp,@.payor_type_tmp where @.costcenter_tmp.ccid =
===============

You cannot use a table variable as a column prefix. Use an alias instead:

from @.costcenter_tmp, ct @.payor_type_tmp pt where ct.ccid =

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp