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/techinf...2000/books.asp