"Case When Then" Issue When Concatenate After Calculation

Question

Post reply

"Case When Then" Issue When Concatenate After Calculation

EjSQLme

SSCrazy

Points: 2172
More actions
July 9, 2010 at 9:27 am

#387313

Hi,
I have a data that is stored in MB. I was trying to convert from MB to GB if the data is over 1024 then add MB or GB depending on the data size, but I get: "Error converting data type varchar to float." on the sql code below.
How can I concatenate this after conversion.
'DataSize' =
CASE WHEN DataSize >= '1024.00'
THEN (DataSize/1024.00) +'GB'
ELSE DataSize +'MB'
END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

Try this:

'DataSize' =

CASE WHEN CAST(DataSize AS int) >= 1024

THEN CAST((CAST(DataSize AS int)/1024) as varchar(10)) +'GB'

ELSE DataSize +'MB'

END

-- Gianluca Sartori

EjSQLme SSCrazy Points: 2172 More actions · Answer 2

Thanks but I'm still the same error message. I think the issue is +'GB or +'MB'; when I remove this it runs fine.

spaghettidba SSC Guru Points: 105712 More actions · Answer 3

Take a look at the article linked in my signature and find out what information is needed to get a quick answer on the forums.

Can you please post table definition script and some sample data?

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105712 More actions · Answer 4

If your column is of type int you can try this:

DECLARE @test-2 TABLE (

DataSize int

)

INSERT INTO @test-2 VALUES (1025)

INSERT INTO @test-2 VALUES (2568)

INSERT INTO @test-2 VALUES (12)

SELECT

'DataSize' =

CASE WHEN DataSize >= 1024

THEN CAST(DataSize /1024 as varchar(10)) +'GB'

ELSE CAST(DataSize as varchar(10))+'MB'

END

FROM @test-2

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105712 More actions · Answer 5

If your column is varchar try this:

DECLARE @test-2 TABLE (

DataSize varchar(10)

)

INSERT INTO @test-2 VALUES (1025)

INSERT INTO @test-2 VALUES (2568)

INSERT INTO @test-2 VALUES (12)

SELECT

'DataSize' =

CASE WHEN CAST(DataSize AS int) >= 1024

THEN CAST((CAST(DataSize AS int)/1024) as varchar(10)) +'GB'

ELSE DataSize + 'MB'

END

FROM @test-2

-- Gianluca Sartori

EjSQLme SSCrazy Points: 2172 More actions · Answer 6

Thank you... that worked. The decimal number is 4 digit after the calculation; What is the easiest way to change it to 2 digit?

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 7

CAST the result to decimal(nn, 2).

For example, something like this:

SELECT

'DataSize' =

CASE WHEN DataSize >= 1024

THEN CAST(CAST(DataSize /1024 AS decimal(9, 2)) as varchar(10)) +'GB'

ELSE CAST(CAST(DataSize AS decimal(9, 2)) as varchar(10))+'MB'

END

Scott Pletcher, SQL Server MVP 2008-2010

EjSQLme SSCrazy Points: 2172 More actions · Answer 8

EjSQLme

SSCrazy

Points: 2172

July 12, 2010 at 7:18 am

#1191452

Great! Thank you both for your help