Case Statement problem - SQL Server Forums

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)

Case Statement problem

Author

Topic

new_user
Starting Member

3 Posts

Posted - 2007-05-14 : 15:58:53

I have dept_code_tab table with dept_code like
FIN001
FIN002
PAY006

first three letter represent the department

I want a query that disaplays like
FIN001 Finance
FIN002 Finance
PAY006 PAyRoll

My case statement is not working. How to do this?
select case dept_code when substring(dept_code,1,3) = 'FIN' then 'Finance' else 'others' from dept_code_tab

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-14 : 16:01:37

you are missing "END" at the end of your case expression:

select case dept_code when substring(dept_code,1,3) = 'FIN' then 'Finance' else 'others' END from dept_code_tab

And, by the way, CASE is an expression, not a statement, in SQL. see: http://weblogs.sqlteam.com/jeffs/archive/2007/05/03/60195.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 16:02:03

select case substring(dept_code, 1, 3) when 'FIN' then 'Finance'
when 'pay' then 'Payroll'
else 'others' end
from dept_code_tab

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-15 : 09:32:00


select 
      case when dept_code like 'FIN%' then 'Finance' 
           when dept_code like 'pay%' then 'Payroll' 
      else 'others' 
      end
from dept_code_tab

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources