Another seq increment issue

[TLeaders]

I am attempting to assign a batch and line number for a set of transactions. The batch number is unique for each batch code date combination and the line number is unique for each for each batch code.
The interesting thing is that if I run the query with a “top 1” everything works fine, any other value and I get 1 for both the batch number and the line number. It behaves as if when it is processing row 2 it does not know that row 1 was created. Is there a setting to force this write / clean the buffer? I am fairly new to SQL server and I may be missing something obvious. I’ve read a number of posts and FAQ’s if this does not work my next step is to create a temp table and assign the numbers from there. The worst part is that before I went to service pack 3 this code was working.

Thanks for any suggestions.
Tom

Batch_code date batch_no line_no
AA 3/2/2004 1 1
AA 3/2/2004 1 2
AA 3/3/2004 2 1
BB 3/2/2004 3 1
BB 3/2/2004 3 2


I am running SQL Server 2000 sp3 (8.00.760)


CREATE TABLE dbo.Z_out(
Batch_No int null,
Batch_Code char(10) NULL,
Line_no int NULL,
Batch_Date int null,
a4glidentity numeric (9,0)
)
go

insert into Z_out (Batch_No, Batch_Code, Line_no,Batch_Date,a4glidentity)
select
--top 1
batch_no =
case
when exists (SELECT top 1 Batch_No FROM Z_out where Batch_Code = JNL_SRC and Batch_Date = Dist_Dt)
then (SELECT top 1 Batch_No FROM Z_out where Batch_Code = JNL_SRC and Batch_Date = Dist_Dt)
else
(isnull((SELECT MAX(Batch_No) FROM Z_out where Batch_Date = Dist_Dt ),0) + 1)
end,
JNL_SRC,
(isnull((SELECT max(Line_no) FROM Z_out where Batch_Code = JNL_SRC),0) + 1),
Dist_Dt,
a.a4glidentity
from MCDISFIL_sql a
where a.filler_0001 is null
option (maxdop 1)

 

[nigelrivett]

when exists (SELECT top 1 Batch_No FROM Z_out where Batch_Code = JNL_SRC and Batch_Date = Dist_Dt)

this should be
when exists (SELECT * FROM Z_out where Batch_Code = JNL_SRC and Batch_Date = Dist_Dt)

but that doesn't matter

then (SELECT top 1 Batch_No FROM Z_out where Batch_Code = JNL_SRC and Batch_Date = Dist_Dt)

This needs an order by if you want it to return a consistent row - that may be the problem. As it is it will return a random value.

======================================
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.