numbering code using charindex can't go above 99, why? 2

[aalmeida]

I have a colum calld [SUBID] that is a compund of two numbers Sub.Par in the format "000000.000000".
so to extract the last sub number wrote the following select statement:

Select top 1 case when len (cast(isnull(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1), '00')+1 as varchar(7)))< 2 then '0'+ cast( isnull(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1), '00')+1 as varchar(7)) else cast( isnull(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1), '00')+1 as varchar(7))end +'.00' from dbo.Proposals order by SUBID DESC

it works fine to numbers up to 99.00, it will not find the 100.00, why? any help will be much apreciated.

thanks,

AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;

 

[donutman]

'000001.000023' returns '02.00'
I don't understand what you want to do? Can you give some examples.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[aalmeida]

donutman,
here you go

initialy I have a table like this:

ID SUBID
01 00.00
02 00.00
03 00.00
04 00.00
100 00.00
101 00.00

Where ID is the PK, they represent submission in process.
When the owner closes the submission it needs a submission number SUBID. SUBID is a concatenated number to the right side of the dot we have the Parent number that is sequencial, to the left side of the dot we have the child number if a child existes.

so the table could look like this:

ID SUBID
01 01.00
02 02.00
03 99.00
04 03.00
100 03.01
101 99.01

it all went fine until the number 99, the next number should be 100.00, 101.00 and so on. but it can not pass the number 100 now all the ones suposes to be 101.00, 102.00 are 100.00 so I'm having several false 100.00. why?
I hope this gives u an idea.

Thanks


AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;

 

[donutman]

Sorry AL, I still don't understand. I don't see where the 99 is coming from. Maybe the parent and child tables would help?
Generally it's not a good idea to put numeric data into a varchar field. This is also true of date data. We see so many posts in this forum regarding difficult that programmers have as a result of violating datatype rules. Queries are much more efficient when it's not necessary to do string manipulation.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[aalmeida]

donutman:

the 99 is coming from this select statment:
Select top 1 case when len (cast(isnull(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1), '00')+1 as varchar(7)))< 2 then '0'+ cast( isnull(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1), '00')+1 as varchar(7)) else cast( isnull(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1), '00')+1 as varchar(7))end +'.00' from dbo.Proposals order by SUBID DESC

and my question is why is it stalling on 99.00? whay is it that it gives an error when CHARINDEX ( '.' , [SubID], 1) is greater that 3?

AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;

 

[SQLBill]

You convert SUBID to VARCHAR in your select script. What is the actual datatype of the SUBID column?

-SQLBill

 

[aalmeida]

Thanks donutman and SQLBill, both of you where great here is a star for both of you.

The reason is that calling my attention to the datatypes I realized that order by SubId DES since subID is a nvarchar(20) the greater numbe would be 99 instead of 100 therefore the top 1 would allways get 99 and never the 100.
I changeged my query to:
Select top 1 case when len (cast(isnull(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1), '00')+1 as varchar(7)))< 2 then '0'+ cast( isnull(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1), '00')+1 as varchar(7)) else cast( isnull(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1), '00')+1 as varchar(7))end +'.00' from dbo.Proposals order by cast(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1) as int) DESC

note the "cast(substring ([SubID], 1, CHARINDEX ( '.' , [SubID], 1)-1) as int)" in the order by, that make it so that 100 is the top 1 not 99.

Great thanks and thanks

AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;

 

[SQLBill]

I'm glad we could help you figure out the issue.

Thanks for the star.

-SQLBill