April 13, 2012 at 6:20 am
I am trying to create two new columns,using a case statement of the same data.
For example
Select
CASE (When fruit = 1 then 'A'
when fruit = 2 then 'B'
else 'C'
END) as Letter,
CASE (When fruit = 1 then 'APPLE'
when fruit = 2 then 'BANANA'
else 'CHERRY'
END) as WORD
from tblFRUITS
however, when I do something like this..I get errors. I am not thinking it is my syntax. Is it even possible to use the same attribute in two seperate case statements?
April 13, 2012 at 6:23 am
Nevermind, figured it out!
Yes it is possible in that manner posted before...i just had a slight issue with the structure of my case statement.
April 13, 2012 at 6:38 am
Yes, I think you just need to lose those parentheses. You can actually shorten it even further, like this:
CASE Fruit
WHEN 1 THEN 'Apple'
WHEN 2 THEN 'Banana'
ELSE 'Cherry'
END AS Word
You may want to consider, if you have the power to do so, adding a lookup table and joining to that instead of using CASE expressions. That way, your code doesn't need to change when a new fruit is added. You'd want to add a foreign key constraint to your existing table so that you don't get wrong data in it.
CREATE TABLE Fruits (
FruitID int PRIMARY KEY,
FruitLetter char(1) UNIQUE,
FruitName varchar(20) UNIQUE
)
As an aside, the correct terminology is CASE expression. Doesn't bother me, since I know what you mean whichever way you put it. But some people get very upset when they see the phrase CASE statement!
John
April 13, 2012 at 6:42 am
good tips all around!
CASE Expression...CASE Expression....CASE Expression, must drill it into my brain.
If I want the part, I have to act the part! hahahaha
Thank you!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply