CASE, WHEN, THEN!

Question

Post reply

CASE, WHEN, THEN!

SQLSeTTeR

SSCertifiable

Points: 5481
More actions
January 6, 2010 at 3:53 pm

#219073

What am I doing wrong? Grr.
select *,mycolumn=
CASE
WHEN mycolumn= 'PSI' THEN mbrproductcode
WHEN mycolumn= 'PPO' THEN 10001912
WHEN mycolumn= 'PP1' THEN 10001910
WHEN mycolumn= 'NOT' THEN 10001909
WHEN mycolumn= 'MIN' THEN 10001908
WHEN mycolumn= 'IND' THEN 10001907
WHEN mycolumn= 'EP1' THEN 10001906
ELSE 'Unknown'
END,
FROM from mytable

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

ToddJames (1/6/2010)
What am I doing wrong? ...

It depends on the error message you receive.

I'd expect something like

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Unknown' to data type int.

The reason is simple: the data type within a CASE statement needs to be identical or at least implicit convertable. The resulting data type will be with respect to data type precedence. In your case you have INT and VARCHAR and something else (I don't know the data type of mbrproductcode).

You have two options: Either change your integer values to character or vice versa.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 2

How about this? Same issue?

select *,mycolumn=

CASE

WHEN mycolumn= 'PSI' THEN 10001911

WHEN mycolumn= 'PPO' THEN 10001912

WHEN mycolumn= 'PP1' THEN 10001910

WHEN mycolumn= 'NOT' THEN 10001909

WHEN mycolumn= 'MIN' THEN 10001908

WHEN mycolumn= 'IND' THEN 10001907

WHEN mycolumn= 'EP1' THEN 10001906

ELSE 'Unknown'

LutzM SSC Guru Points: 107049 More actions · Answer 3

Nothing really changed except you removed the reference to your column.

Please reread my previous reply.

If it's working or not: give it a try in SSMS. But the answer will be: no.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

ToddJames (1/6/2010)
What am I doing wrong? Grr.
select *,mycolumn=
CASE
WHEN mycolumn= 'PSI' THEN mbrproductcode
WHEN mycolumn= 'PPO' THEN 10001912
WHEN mycolumn= 'PP1' THEN 10001910
WHEN mycolumn= 'NOT' THEN 10001909
WHEN mycolumn= 'MIN' THEN 10001908
WHEN mycolumn= 'IND' THEN 10001907
WHEN mycolumn= 'EP1' THEN 10001906
ELSE 'Unknown'
END,
FROM from mytable

Must look eye... just remove the comma from the END keyword.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 5

Jeff Moden (1/6/2010)
ToddJames (1/6/2010)
What am I doing wrong? Grr.
select *,mycolumn=
CASE
WHEN mycolumn= 'PSI' THEN mbrproductcode
WHEN mycolumn= 'PPO' THEN 10001912
WHEN mycolumn= 'PP1' THEN 10001910
WHEN mycolumn= 'NOT' THEN 10001909
WHEN mycolumn= 'MIN' THEN 10001908
WHEN mycolumn= 'IND' THEN 10001907
WHEN mycolumn= 'EP1' THEN 10001906
ELSE 'Unknown'
END,
FROM from mytable
Must look eye... just remove the comma from the END keyword.

It still won't work, I guess. (have a look at the data type of the ELSE clause compared to the rest). I tried to explain it in my first reply already. Seems like the message didn't go through though...

I would expect an error message saying

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Unknown' to data type int.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Ah.... must look eye myself... I missed the two different datatypes. Sorry. NULL means "Unknown" and would work here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)