Searched CASE expression not working

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

SQL Server 2005 Forums

Transact-SQL (2005)

Searched CASE expression not working

Author

Topic

demonickat
Starting Member

4 Posts

Posted - 2010-06-16 : 06:29:02

Hi,

I am trying to write this Searched CASE expression here.
An example of what I am trying to write is here
evening, afternoon, lunchtime, morning are all columns in the database. The idea is to merge them all into one where they are = 1

select
[Description]
,CASE
WHEN evening = 1 THEN 'Evening'
WHEN afternoon = 1 THEN 'afternoon'
WHEN Lunchtime = 1 THEN 'Lunchtime'
WHEN morning = 1 THEN 'morning'
END AS TimeOfDay

I have tested each condition seperately, i.e. just running the CASE for Lunchtime only etc.. and I get the results I want.
However when I put all the conditions together the top one works properly but the others do not give the results I was expecting

Have I specified my CASE statement correctly?

Demonickat

Kristen
Test

22859 Posts

Posted - 2010-06-16 : 06:53:24

[code]select
[Description]
, CASE WHEN evening = 1 THEN 'Evening' ELSE '' END
+ CASE WHEN afternoon = 1 THEN 'afternoon' ELSE '' END
+ CASE WHEN Lunchtime = 1 THEN 'Lunchtime' ELSE '' END
+ CASE WHEN morning = 1 THEN 'morning' ELSE '' END
END AS TimeOfDay
[/code]

Kristen
Test

22859 Posts

Posted - 2010-06-16 : 06:54:14

You'll need to handle some separators or somesuch I expect, that will give you a trailing one to "remove" which will be a bit fiddly

demonickat
Starting Member

4 Posts

Posted - 2010-06-18 : 09:37:52

Thanks, I got it working in the end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources