Convert Decimal to Int 1

[chamilz]

Hi,

I want to format the data as below for a report.

here is just a sample of what i am doing...

create table #test
(Header_1 varchar(20),
Month_1  numeric(10,2))
;

insert into #test values('Sales',120)
insert into #test values('Sales%',2.45)
;

select case when charindex('%',[Header_1])=0  then cast ([Month_1] as int) 
								else [Month_1] end 
from #test

what i want is if the 'Header_1' has '%' then i want to convert [Month_1] values as int.

any idea why the above code doesn't work?

Thanks!!

 

[jbenson001]

Your logic is wrong. If you want any values to be integer where header_1 contains a "%", then you need to have

charindex('%',[Header_1]) > 0 instead of
charindex('%',[Header_1]) = 0

 

[chamilz]

Thanks jbenson001 for pointing that error!!

 

[gmmastros]

Read this:

http://blogs.lessthandot.com/index....erver/sql-server-case-when-data-type-problems

It'll show that the data returned from a case/when must be consistent. You cannot have it return an int in one row and a numeric in another. Can't be done.

What you can do is have it return a string (varchar) in all cases. Like this:

create table #test
(Header_1 varchar(20),
Month_1  numeric(10,2))
;

insert into #test values('Sales',120)
insert into #test values('Sales%',2.45)
;

select case when charindex('%',[Header_1])=0  then cast(cast ([Month_1] as int) As Varchar(20))
                                else cast([Month_1] as VarChar(20)) end
from #test

Drop Table #Test

** Note: I did not apply the change that jbenson001 suggested.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chamilz]

That was very informative George. I wasn’t sure that there is a workaround to convert data type using CASE so i thought that my logic was wrong. So i built that logic at the report level.

Thanks for the suggestion.