Need Help In T-SQL Code Being Executed In Query Analyzer 1

[canguro]

Hello!

I have some code that I am trying to execute in one piece in Query Analyzer, but somehow it won't. The strange thing is that it works in 2 separate parts. The following code, Step 01 and Step 02, seems to run just fine as one piece of code:

------------- STEP 01 --------------

BEGIN

select *
into #Last4MktDays
from MstrWatchList
where transdate in
(select distinct top 4 transdate
from MstrWatchList
order by transdate)
order by ticker, transdate

END

------------- STEP 02 --------------

BEGIN

alter table #Last4MktDays ADD RowCntr INT IDENTITY NOT NULL

END

Also, when I run the following code separately, after the above code has already completed, it gives me a valid 'select', showing me the 10 rows I expect:

------------- STEP 03 --------------

BEGIN

select * from #Last4MktDays
where RowCntr <= 10
order by RowCntr

END

However, when I try to put Step 03 right after Steps 01 and 02 and run it as one piece of program code it fails in the Step 03 portion, telling me the following:

Server: Msg 207, Level 16, State 3, Line 28
Invalid column name 'RowCntr'.
Server: Msg 207, Level 16, State 1, Line 28
Invalid column name 'RowCntr'.

Can someone explain to me why this happens? I would appreciate it very much.

Many thanks!

 

[nigelrivett]

The temp table structure is resolved before the batch runs so the alter table statement is not included in te query plan.
The final statement would have to be run in a separate batch - dynamic sql or another SP.

see

http://www.nigelrivett.net/AccessTempTablesAcrossSPs.html

replace the final statement with

declare @sql varchar(1000)
select @sql = 'select * from #Last4MktDays
where RowCntr <= 10
order by RowCntr'
exec (@sql)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Or you can replace the first two statements by

select *, identity(int,1,1) RowCntr
into #Last4MktDays
from MstrWatchList
where transdate in
(select distinct top 4 transdate
from MstrWatchList
order by transdate)
order by ticker, transdate


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[canguro]

nigelrivett,

Thank you for your reply and suggestions. I liked the way the 2nd one looked, so I did as you said, replacing the first 2 statements with that code and then following it with the original STEP 03 code. I ran it and it does work in one shot. I still seem to have a problem, though. It is essential for me to have the RowCntr 'sync' with order by ticker and transdate.

What I get in the sample select, for RowCntr <= 10 is the following:

EMKR 2002-11-05 00:00:00 1.87 2.02 1.71 1.84 115600 1
DRAX 2002-11-01 00:00:00 1.49 1.68 1.49 1.58 3900 2
MNDO 2002-11-04 00:00:00 1.10 1.22 1.10 1.22 6100 3
MLIN 2002-11-01 00:00:00 2.71 2.90 2.67 2.88 303100 4
MLIN 2002-11-04 00:00:00 2.85 2.97 2.80 2.93 27200 5
DRAX 2002-11-06 00:00:00 1.65 1.68 1.63 1.63 9100 6
ICAD 2002-11-06 00:00:00 1.59 1.60 1.55 1.56 19400 7
PKSI 2002-11-01 00:00:00 .34 .36 .34 .34 27600 8
PKSI 2002-11-04 00:00:00 .27 .36 .27 .33 21200 9
ITIG 2002-11-04 00:00:00 .90 .90 .85 .86 22800 10

In other words, I need order by ticker, transdate, and the RowCntr to increment starting at 1 by 1.

Can you offer an modification to the code you suggested?

Many thanks again,

Joe