Invalid object name '#tempcnt'.

[nc297]

When I run this I get results...

select o.sort, o.reg, o.region, o.area, o.dist, o.doc, o.rpt,
o.[dec 09], o.[dec 09 rank], o.[dec 09 icnt],o.[dec 09 tcnt],
o.[jan 10], o.[jan 10 rank], o.[jan 10 icnt],o.[jan 10 tcnt],
o.[feb 10], o.[feb 10 rank], o.[feb 10 icnt],o.[feb 10 tcnt],
o.[mar 10], o.[mar 10 rank], o.[mar 10 icnt],o.[mar 10 tcnt],
o.[apr 10], o.[apr 10 rank], o.[apr 10 icnt],o.[apr 10 tcnt],
o.[may 10], o.[may 10 rank], o.[may 10 icnt],o.[may 10 tcnt],
o.[jun 10], o.[jun 10 rank], o.[jun 10 icnt],o.[jun 10 tcnt],
o.[jul 10], o.[jul 10 rank], o.[jul 10 icnt],o.[jul 10 tcnt],
o.[aug 10], o.[aug 10 rank], o.[aug 10 icnt],o.[aug 10 tcnt],
o.[sep 10], o.[sep 10 rank], o.[sep 10 icnt],o.[sep 10 tcnt],
o.[oct 10], o.[oct 10 rank], o.[oct 10 icnt],o.[oct 10 tcnt],
o.[nov 10], o.[nov 10 rank], o.[nov 10 icnt],o.[nov 10 tcnt],
o.[dec 10], o.[dec 10 rank], o.[dec 10 icnt],o.[dec 10 tcnt],
n.[jan 11], n.[jan 11 rank], n.[jan 11 icnt],n.[jan 11 tcnt]

from iclaimspivotold o
join ribinfo n on n.reg=o.reg
where o.rpt = '2' and o.sort in ('A','B','C','D','E','G','H','I','J','K') and o.reg is not null and o.area is not null

When I add the top query with an else clause I'm getting this error message:

invalid object name '#tempcnt'

I don't know why because I added an else clause. Here's the entire query with the else clause in it.

if @rpt = '2'

select o.sort, o.reg, o.region, o.area, o.dist, o.doc, o.rpt,
o.[dec 09], o.[dec 09 rank], o.[dec 09 icnt],o.[dec 09 tcnt],
o.[jan 10], o.[jan 10 rank], o.[jan 10 icnt],o.[jan 10 tcnt],
o.[feb 10], o.[feb 10 rank], o.[feb 10 icnt],o.[feb 10 tcnt],
o.[mar 10], o.[mar 10 rank], o.[mar 10 icnt],o.[mar 10 tcnt],
o.[apr 10], o.[apr 10 rank], o.[apr 10 icnt],o.[apr 10 tcnt],
o.[may 10], o.[may 10 rank], o.[may 10 icnt],o.[may 10 tcnt],
o.[jun 10], o.[jun 10 rank], o.[jun 10 icnt],o.[jun 10 tcnt],
o.[jul 10], o.[jul 10 rank], o.[jul 10 icnt],o.[jul 10 tcnt],
o.[aug 10], o.[aug 10 rank], o.[aug 10 icnt],o.[aug 10 tcnt],
o.[sep 10], o.[sep 10 rank], o.[sep 10 icnt],o.[sep 10 tcnt],
o.[oct 10], o.[oct 10 rank], o.[oct 10 icnt],o.[oct 10 tcnt],
o.[nov 10], o.[nov 10 rank], o.[nov 10 icnt],o.[nov 10 tcnt],
o.[dec 10], o.[dec 10 rank], o.[dec 10 icnt],o.[dec 10 tcnt],
n.[jan 11], n.[jan 11 rank], n.[jan 11 icnt],n.[jan 11 tcnt]

from iclaimspivotold o
join ribinfo n on n.reg=o.reg
where o.rpt = '2' and o.sort in ('A','B','C','D','E','G','H','I','J','K') and o.reg is not null and o.area is not null

else



Select Sort='1', Reg='NAT', DowrDt, iCnt, tCnt
into #tempcnt
From iClaims
where Rpt = @Rpt and sort = '1' and reg is not null and area is not null
group by dowrdt, iCnt, tCnt

insert #tempcnt
Select Sort =Region, Reg=Reg, DowrDt, iCnt, tCnt
From iClaims
where rpt = '1' and sort in ('A','B','C','D','E','G','H','I','J','K') and reg is not null and area is not null
Group by DowrDt, Region, Reg, iCnt, tCnt

DECLARE @pivot_cols NVARCHAR(MAX);
SELECT @pivot_cols =
STUFF((SELECT ',MAX(CASE WHEN year_month = ''' + year_month +
''' THEN perc END) AS [' + year_month + ']' +
',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN iCnt END) AS [' + year_month + ' iCnt]' +
',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN tCnt END) AS [' + year_month + ' tCnt]'
FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9),
CAST(dowrdt AS DATETIME), 6), 6),
CONVERT(CHAR(7), CAST(dowrdt AS DATETIME), 126)
FROM #tempCnt) AS T(year_month, sort)
ORDER BY sort
FOR XML PATH('')
), 1, 1, '');


DECLARE @pivot_query NVARCHAR(MAX);
SET @pivot_query =
N'SELECT sort, reg, ' + @pivot_cols +

N'FROM (SELECT sort, reg, iCnt, tCnt, dowrdt, ' +
N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME), 6),
6) AS year_month, ' +
N'CAST((1.0 * iCnt / NULLIF(tCnt, 0) * 100) AS DECIMAL(5,
1)) AS perc ' +
N'FROM #tempCnt) AS F ' +
N'GROUP BY sort, reg ' +
N'ORDER BY sort;';

EXEC(@pivot_query);

drop table #tempcnt

 

[MarkSweetland]

#tempcnt is only created during the else portion, not if the rpt does in fact equal 2

Try it by formatting like:

IF (something...)
BEGIN
...some code here
END
ELSE
BEGIN
... other code here
END


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[nc297]

Thank you that did it!!