April 24, 2007 at 5:42 am
Hello everyone, i m using SQL Server 2000, here are few lines as when i write under the if stmts:
set rojectCode=@ProjectCode+'0001'">ProjectCode=@ProjectCode+'0001'
select @ProjectCode
it retuns nothing just project values with concatenate the values 0001
but if i write:
declare @ProjectCode char(20)
set @ProjectCode='1110201BA'
declare @codelastdigit char(4)
select @codelastdigit=substring(@ProjectCode,10,13)
if @codelastdigit is null or @codelastdigit=0
begin
select ProjectCode =@ProjectCode+'0001'
end
it add the values 0001 but the result shown in this format: 1110201BA 0001 (as it also added more space between project code and the 0001 value.)
kindly tell me whats wrong with this?
and also help me in the else case i want to get max values from project code (last 4 values) and add 1 in that values but if the projet code first 9 values are the same and if new values comes then it again add 0001 not go to else case.
For example i have 1110201BA0001 now again i have (1st 9 values: 1110201BA then take maximum values of that project code and in that take increment in the last 4 values
as i get 1110201BA0002 but if i get 1110001BA then we add 0001 in this.
plz help me how i do this. and tell me abt the first part also whats wrong with that.
Thanx in Advance.
April 24, 2007 at 7:22 am
select @projectcode = @projectcode + '0001'
In the second example you are directly returning @projectcode+'001' and having the output column named ProjectCode, not assigning the variable.
April 24, 2007 at 7:34 am
declare @ProjectCode char(20)
you have declared this as char and sql will add trailing spaces to values less than 20 char in length
set @ProjectCode='1110201BA'
will have trailing spaces (see above)
declare @codelastdigit char(4)
declared as char (see above)
select @codelastdigit=substring(@ProjectCode,10,13)
substring is start position,length not start position,end position so it should be substring(@ProjectCode,10,4) (although the result will be the same as it would be truncated)
if @codelastdigit is null or @codelastdigit=0
@codelastdigit can only be null if @ProjectCode is null
blank @codelastdigit will explicitly casted to zero (because to are checking against int value) should test using true value (4 spaces if char)
begin
select projectcode =@projectcode+'0001'
@projectcode is char therefore result will be 20 chars of @projectcode plus 4 chars added
end
Is this what you are looking for ?
DECLARE @ProjectCode2 char(20)
DECLARE @codelastdigit varchar(4)
SELECT @codelastdigit = SUBSTRING(MAX(ProjectCode),10,4)
FROM
WHERE LEFT(ProjectCode,1,9) = @ProjectCode
IF @codelastdigit IS NULL PRINT 'ProjectCode not found'
IF @codelastdigit = '' SET @codelastdigit = '0000'
SET @codelastdigit = RIGHT('000'+CAST(CAST(@codelastdigit as int)+1 as varchar(5)),4)
SET @ProjectCode = LEFT(@ProjectCode,9) + @codelastdigit
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
