Case when then statement is not working correctly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case when then statement is not working correctly

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-10-21 : 21:58:11

column
99.00(EUR)per stay
99.50(EUR)
990.00(EUR)
SOLD OUT 0 miles NW - 0 miles NW
SOLD OUT 0 miles S - 0 miles S
SOLD OUT 1 miles E - 1 miles E
SOLD OUT 1 miles N - 1 miles N

When I try this query:

select hey = case when roomrate like '%(eur)%' then convert(float, replace(left(roomrate, charindex('.',roomrate)-1), ',', ''))*1.31
				when roomrate like '%sold%' then 'Sold Out'			
 else roomrate end, id_num
from marriott
where roomrate like '%eur%' or roomrate like '%sold%'
group by roomrate, id_num

I get this error

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

However when do this it works

select hey = case when roomrate like '%(eur)%' then convert(float, replace(left(roomrate, charindex('.',roomrate)-1), ',', ''))*1.31
				when roomrate like '%sold%' then 'Sold Out'			
 else roomrate end, id_num
from marriott
where roomrate like '%eur%'
group by roomrate, id_num

Why Is this?

I need to get both filtering actions to work. If it contains "eur" then change the currency to correct USD amount, and if it contains "sold" then change it to "sold Out".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 23:56:03

thats because all the conditions of case statement should return same datatype result. Here you're trying to return float value for one condition and varchar for other and hence the error. In second case because of where condition you've only one condition data existing which is all returned float and so no error. I think best way is to return value without connverting to float in first case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources