cancel
Showing results for 
Search instead for 
Did you mean: 

Batch number auto generation query is not working properly

Former Member
0 Kudos

declare @Var as nvarchar(25)

declare @Var1 as nvarchar(25)

declare @Var2 as nvarchar(25)

declare @Var3 as nvarchar(25)

declare @Var4 as nvarchar(25)

select @Var4 =N''

select @Var4 = @Var4

if @var4 = '01'

begin

set @var4 ='D'

select @Var =max(cast(batchnum as NVARCHAR(25))) from oibt where batchnum like '%zd%'

select @Var2 = substring(@Var,3,3)

select @Var3 = substring(@Var,3,6)

end

else

begin

set @var4 ='I'

select @Var = max(cast(batchnum as NVARCHAR(25))) from oibt where batchnum like '%zi%'

select @Var2 = substring(@Var,3,3)

select @Var3 = substring(@Var,3,6)

end

select @Var1 = substring(@Var,5,8)

select @Var1 = @Var3 + 1

select @Var2= 'Z'+ @var4 + @Var1

select @var2

this query is not fetching the maximum value of batch number so kindly help me out in this as soon as possible

regards

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

thnaks to all i got the soluntion

Former Member
0 Kudos

I don't understand what is the logic for:

select @Var4 =N''

select @Var4 = @Var4

Your @Var4 would always be empty due to the fact your second select override the first. What is your logic? I am afraid you may misunderstand the usage of SELECT to IF.

Thanks,

Gordon

former_member204969
Active Contributor
0 Kudos

May be you should select the maximum of substring(batchnum,5,8).