SQL 2000 TEMP table Invalid column name uisng SQL string

[CJwater]

I've tried this 50 different ways and am not getting anywhere... I tried not using Temp table and it WORKS. I'm not sure but I believe my users won't be able to execute delete table with out me changing permissions to DB owner? I'd rather resolve this temp table issue. (Insert into didn't work either)

Here's my code

CREATE TABLE #RebRawdata2 (
[RebateAcct] [nvarchar] (4) ,
[BalFwd] [decimal](16, 2)
)
-- Different Quarter dif starting QT fiscal balance figures
if Month(Getdate()) in (1, 2, 3)
BEGIN
SET @monthBal1 = ' GL.beg_bal_12 * -1 '
END
ELSE IF Month(Getdate()) in (4, 5, 6)
BEGIN
SET @monthBal2 = ' GL.beg_bal_3 * -1 '
END
ELSE IF Month(Getdate())in (7, 8, 9)
BEGIN
SET @monthBal3 = ' GL.beg_bal_6 * -1 '
END
ELSE IF Month(Getdate())in (10, 11, 12)
BEGIN
SET @monthBal4 = ' GL.beg_bal_9 * -1 '

End
-- 3 balances should be empty so I'll only pick up the one that has data
SET @monthBal = Rtrim(Ltrim(@monthBal1)) + Rtrim(Ltrim(@monthBal2))+ Rtrim(Ltrim(@monthBal3)) + Rtrim(Ltrim(@monthBal4))

select @strSQL = ' Insert Into #RebRawdata2 select RD.RebateAcct , ' + ltrim(rtrim(@monthBal)) + ' From GLBALFIL_SQL AS GL Inner Join #RebRawdata AS RD ON RD.RebateAcct = Left(GL.Mn_No, 4) Group By RebateAcct Order By RebateAcct'

execute(@strSQL)

While @i <= @RebCount
Begin
-- I'm getting Invalid column name 'RebateAcct'here
Set @RebAcct = (Select Top 1 RebateAcct From #RebRawdata2 Order By RebateAcct)
-- I'm getting Invalid column name 'BalFwd'.here also
Set @RebBalFwd = (Select Top 1 BalFwd From #RebRawdata2 Order By RebateAcct)

Can anyone tell me what I'm missing here?
Thank You
CJ

 

[RiverGuy]

You should stay away from dynamic SQL as much as possible, and in this case you do not need it:

INSERT INTO #RebRawdata2
SELECT RD.RebateAcct,
CASE
  WHEN MONTH(GETDATE()) BETWEEN 1 AND 3 THEN GL.beg_bal_12  * -1
  WHEN MONTH(GETDATE()) BETWEEN 4 AND 6 THEN GL.beg_bal_3  * -1
  WHEN MONTH(GETDATE()) BETWEEN 7 AND 9 THEN GL.beg_bal_6  * -1
  WHEN MONTH(GETDATE()) BETWEEN 10 AND 12 THEN GL.beg_bal_9  * -1
END
From GLBALFIL_SQL AS GL
Inner Join #RebRawdata AS RD ON RD.RebateAcct = Left(GL.Mn_No, 4)
--Shouldn't need GROUP BY if not aggregations / Are you sure you don't need to SUM the value for BalFwd?

I'm not sure about the bottom part. Has the temp table gone out of scope? It's hard to tell without seeing all of your code. I don't see your variable declarations for @i and @RebCount, so I assume there are parts you omitted.

 

[CJwater]

Thanks, the case is definitely the simplest and most efficient... No group by no sum needed either just one general ledger balance. Just trying to many things and getting confused, nothing seems to get me passed this error...

Declare @RebCount int, @DFCount int, @i int, @t int, @RebAcct varchar(4), @RebBalFwd decimal(28, 2), @DFAcct varchar(4), @DFBalFwd decimal(28, 2), @CurrQtr int

Set @RebCount = (Select Count(Distinct RebateAcct) From tblRebateCalcQuarterly)
Set @i = 1

--Grab Unique Rebate Acct No's
Select
Distinct RebateAcct
Into #RebRawdata
From
tblRebateCalcQuarterly
----------------

Set @CurrQtr = (Select Case When Month(Getdate()) in (1, 2, 3) Then Convert(int, Convert(varchar, Year(Getdate()) - 1) + '1231')
When Month(Getdate()) in (4, 5, 6) Then Convert(int, Convert(varchar, Year(Getdate())) + '0331')
When Month(Getdate()) in (7, 8, 9) Then Convert(int, Convert(varchar, Year(Getdate())) + '0630')
When Month(Getdate()) in (10, 11, 12) Then Convert(int, Convert(varchar, Year(Getdate())) + '0930')
End)


--*** Get balance from General Ledger Directly and insert #RebRawdata2


INSERT INTO #RebRawdata2
SELECT RD.RebateAcct,
CASE
WHEN MONTH(GETDATE()) BETWEEN 1 AND 3 THEN GL.beg_bal_12 * -1
WHEN MONTH(GETDATE()) BETWEEN 4 AND 6 THEN GL.beg_bal_3 * -1
WHEN MONTH(GETDATE()) BETWEEN 7 AND 9 THEN GL.beg_bal_6 * -1
WHEN MONTH(GETDATE()) BETWEEN 10 AND 12 THEN GL.beg_bal_9 * -1
END
From GLBALFIL_SQL AS GL
Inner Join #RebRawdata AS RD ON RD.RebateAcct = Left(GL.Mn_No, 4)

--Rebate Accts
--Start Loop, this grabs the top record from #rawdata2 and updates tblRebateCalcQuarterly with the data, then remove that record from #rawdata2 and resets the parameters
While @i <= @RebCount
Begin
-- 1 error here Invalid column name 'RebateAcct'.
Set @RebAcct = (Select Top 1 RebateAcct From #RebRawdata2 Order By RebateAcct)
-- 2 errors here Invalid column name 'BalFwd' and Invalid column name 'RebateAcct'.
Set @RebBalFwd = (Select Top 1 BalFwd From #RebRawdata2 Order By RebateAcct)


-----------------------

Update tblRebateCalcQuarterly
Set Reb_Acct_BalFwd = @RebBalFwd
Where RebateAcct = @RebAcct

Set @i = @i + 1

Delete From #RebRawdata2
Where RebateAcct = @RebAcct

End


There's more code "after" but I'm still getting the error in the same place.

Thank You "Very Much" for your help
CJ

 

[RiverGuy]

Where you are getting your error, try a SELECT TOP 1 * FROM #RebRawdata2, just to see what columns come up.

 

[CJwater]

That was it! Once I queried the table I realized it had different column headers.

No sure how or what happen but my temp table #RebRawdata2 was in the database and "It had different headers". I dropped the table and can now run the SP no errors. Any ideas how it could have stayed around? I thought it should disappear as soon as the SP was done executing? Could it have stayed if I ran it in query analyzer?

Thanks Again, I can now run my year end numbers!
CJ

 

[RiverGuy]

If you run the body of the procedure in query analyzer, the temp table will stay around until you drop it or break the connection. It's common practice to write an explicit drop statement at the end of the query.

DROP TABLE #RebRawdata2