Help converting CASE statement

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

Help converting CASE statement

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-20 : 04:31:06

I have a case statement which I need to alter in my query. The field

[sr.spec_value]

contains either 1 or 0. When it is 1 I want the case expression to return 'YES' when 0 then 'NO'. I've tried a couple of options from the forum but am not getting it right. Here is the original case statement from my query:

case when sr.spec_value = 1 then 'YES' when 0 then 'NO' end [Fixed]

Many thanks
Martyn

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-12-20 : 04:36:58

case when sr.spec_value = 1 then 'YES' when sr.spec_value = 0 then 'NO' end [Fixed]

Too old to Rock'n'Roll too young to die.

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-20 : 04:54:20

Thanks, I still get an error around converting 'YES' to int but I think this is because the spec_value field attached to the work orders is currently for text. When the spec_value item is changed in the ERP system to be a tickbox item instead, this should be OK.

Many thanks
Martyn

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-12-20 : 05:06:32

If you need more help then we need to see the whole query

Too old to Rock'n'Roll too young to die.

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2013-12-20 : 05:22:56

Will do, many thanks.

Martyn

Kristen
Test

22859 Posts

Posted - 2013-12-20 : 06:47:58

quote:
Originally posted by wembleybear

Thanks, I still get an error around converting 'YES' to int but I think this is because the spec_value field attached to the work orders is currently for text. When the spec_value item is changed in the ERP system to be a tickbox item instead, this should be OK.


case when sr.spec_value = '1' then 'YES' when sr.spec_value = '0' then 'NO' end [Fixed]

would fix that. That would also work when sr.spec_value changes to an INT or a BIT (but it will involve an implicit conversion, so would be better to then change it back to numeric values). It would let you test your change in the meantime.

However, the fact that you get that error means that one/many values for sr.spec_value are NOT numeric.

SQL will be very happy if you do:


SELECT CASE WHEN '1'=1 THEN 'OK' ELSE 'Not OK' END

its only when the string is NOT convertable to a numeric that the error is raised


SELECT CASE WHEN 'GARBAGE'=1 THEN 'OK' ELSE 'Not OK' END

so you have some not-numeric "goofy" data in sr.spec_value. Maybe check a few values yourself? Someone is going to have to figure out how to fix them when it is converted to a checkbox!


SELECT DISTINCT TOP 100 sr.spec_value
FROM MyTable AS sr
WHERE sr.spec_value LIKE '%[^01]%'

One solution is to add an ELSE to your case statement (good practice anyway, even for "can never happen" events - at least if, for whatever reason, it does ever happen then the Front End will display something which users then raise as a support question).


case when sr.spec_value = 1 then 'YES' when sr.spec_value = 0 then 'NO' 
ELSE '[' + CONVERT(varchar(20), sr.spec_value) + '] UNEXPECTED VALUE'
end [Fixed]

and, just for completeness, there is another syntax you can use if you are only comparing values in a single column:


case sr.spec_value
WHEN 1 then 'YES'
WHEN 0 then 'NO' 
ELSE '[' + CONVERT(varchar(20), sr.spec_value) + '] UNEXPECTED VALUE'
end [Fixed]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources