To find missing sequence using SQL command

[zsyed]

I'm using following code to to find out missing sequence in part transactiion ID from a table parttransactions anything above 6000000. To some reason when I execute this code I don't get any result but I get message says "Query has finished processing" . Any advice what I'm missing?

declare @min int
declare @max int
create table #ZS (tempID int)

select @min = min(imtPartTransactionID), @max = max(imtPartTransactionID) from PartTransactions where imtPartTransactionID > 6000000

while @min <= @max
begin
if not exists (select * from PartTransactions where imtPartTransactionID = @min)
insert into #ZS (tempID) values (@min)
set @min = @min + 1
end

select * from #ZS
drop table #ZS

 

[hilfy]

Crystal doesn't work well with this type of temp table - especially since you're dropping the table as part of the command. You might want to try putting this code into a stored procedure that will return the select statement as a cursor in an in/out parameter (that's how Crystal likes to get data from stored procs) instead of trying to do this as a command.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[zsyed]

Hi Hilfy,

Thanks for the reply. My question is not intended to use in crystal reports but to execute this command as SQL query. My be I'm asking this qustion at incorrect place. Appreciate your response.

 

[hilfy]

This is a Crystal Reports forum, so if you're just looking for SQL help, you're definitely in the wrong place.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Madawc]

There's a good SQL Forum at Tek Tips, try there. You can see these (and others) by going to the top of the page and clicking 'Programmers' on

Home > Forums > Programmers > Reporting Solutions > Business Objects: Crystal Reports 1 Formulas Forum

Though it could be done in Crystal quite easily.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP