Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert Decimal to Int 1

Status
Not open for further replies.

chamilz

MIS
Feb 10, 2005
102
CA
Hi,

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

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

Code:
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!!
 
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
 
Read this:


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:

Code:
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top