My CASE Statement isn't quite converting properly

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

My CASE Statement isn't quite converting properly

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-03-18 : 07:39:03

Hi guys,

I am having a small problem with the following SQL:

(CASE
WHEN action_officer.officer_name LIKE '%__' and RIGHT(action_officer.officer_name, 2) IN ('XX','XX','38') THEN RIGHT(action_officer.officer_name, 2) ELSE 'Unknown' END) as Team_Code,

(CASE (CASE
WHEN action_officer.officer_name LIKE '% __' and RIGHT(action_officer.officer_name, 2) IN ('XX','XX', '38') THEN RIGHT(action_officer.officer_name, 2) ELSE 'Unknown' END)
WHEN 'XX' THEN 'Test'
WHEN '38' THEN 'S38 - North and West'
ELSE 'Unknown'
END) as Team_Name

In the Team_Name field, the SQL is supposed translate '38' as 'Unknown' when it should really says 'S38 - North and West'.

Does anyone know what the issue could be?

shaggy
Posting Yak Master

248 Posts

Posted - 2013-03-18 : 07:46:08

Try this
(CASE
WHEN action_officer.officer_name LIKE '%[__]' and RIGHT(action_officer.officer_name, 2) IN ('XX','XX','38') THEN RIGHT(action_officer.officer_name, 2) ELSE 'Unknown' END) as Team_Code,

(CASE (CASE
WHEN action_officer.officer_name LIKE '% [__]' and RIGHT(action_officer.officer_name, 2) IN ('XX','XX', '38') THEN RIGHT(action_officer.officer_name, 2) ELSE 'Unknown' END)
WHEN 'XX' THEN 'Test'
WHEN '38' THEN 'S38 - North and West'
ELSE 'Unknown'
END) as Team_Name

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 07:55:47

May be this?

CASE 
WHEN action_officer.officer_name LIKE '%[__]' and RIGHT(action_officer.officer_name, 2) IN ('XX','38') THEN RIGHT(action_officer.officer_name, 2) ELSE 'Unknown' END) as Team_Code,
CASE RIGHT(action_officer.officer_name, 2)
 WHEN 'XX' THEN 'Test'
 WHEN '38' THEN 'S38 - North and West'
 ELSE 'Unknown' END as Team_Name

--
Chandu

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-03-18 : 08:02:59

Hi Shaggy,

Even when I test your SQL it still comes back with Unknown.

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-03-18 : 08:05:51

Hi guys,

One thing I noticed is the action_officer.officer_name has the definition as "S38". Or in other words, it ends with S38 rather than 38. Could this cause a problem?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 08:28:17

Check the following illustration once...


DECLARE @action_officer TABLE(officer_name VARCHAR(40))
INSERT INTO @action_officer VALUES('chandu'), ('gfiawaS38'), ('afbajfvXX'), ('s')
SELECT CASE WHEN RIGHT(officer_name, 2) IN ('XX','38') THEN RIGHT(officer_name, 2) ELSE 'Unknown' END as Team_Code,
	   CASE RIGHT(officer_name, 2)  WHEN 'XX' THEN 'Test'  WHEN '38' THEN 'S38 - North and West' ELSE 'Unknown' END as Team_Name
FROM @action_officer

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources