Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Oracle CASE statement into SQL Server Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:50 PM
Points: 39, Visits: 274
I'm working on changing an Oracle CASE statement into a SQL Server CASE statement. I having a spot of difficulty in trying to figure out how to alter the code. It seems as though I am making it harder than it really needs to be. The Oracle code is...

(CASE
WHEN TO_CHAR(H.effdt, 'YYYY') IN ('2001','2002') THEN
DECODE (H.review_rating,
'1','Exceeded Way, Exceeded Results',
'2','Exceeded Way, Achieved Results',
'3','Exceeded Way, Did not meet Results',
'4','Achieved Way, Did not meet Results',
'5','Achieved Way, Achieved Results',
'6','Achieved Way, Exceeded Results',
'7','Did not meet Way, Exceeded Results',
'8','Did not meet Way, Achieved Results',
'9','Did not meet Way, Did not meet Results',
'Empty')

WHEN TO_CHAR(H.effdt, 'YYYY') IN ('2003','2004', '2005') THEN
DECODE (H.review_rating,
'1','Does not Meet Expectations',
'2','Partially Meets Expectations',
'3','Meets Expectations',
'4','Exceeds Expectations',
'5','Far Exceeds Expectations',
'Empty')
ELSE 'Empty'
END) as review_rating


My attempt to correct...

CASE WHEN YEAR(H.effdt) IN ('2001','2002') AND
(CASE H.review_rating
WHEN '1' THEN 'Exceeded Way, Exceeded Results'
WHEN '2' THEN 'Exceeded Way, Achieved Results'
WHEN '3' THEN 'Exceeded Way, Did not meet Results'
WHEN '4' THEN 'Achieved Way, Did not meet Results'
WHEN '5' THEN 'Achieved Way, Achieved Results'
WHEN '6' THEN 'Achieved Way, Exceeded Results'
WHEN '7' THEN 'Did not meet Way, Exceeded Results'
WHEN '8' THEN 'Did not meet Way, Achieved Results'
WHEN '9' THEN 'Did not meet Way, Did not meet Results'
ELSE 'Empty'
END)
OR
WHEN YEAR(H.effdt) IN ('2003','2004','2005') AND
(CASE H.review_rating
WHEN '1' THEN 'Does not Meet Expectations'
WHEN '2' THEN 'Partially Meets Expectations'
WHEN '3' THEN 'Meets Expectations'
WHEN '4' THEN 'Exceeds Expectations'
WHEN '5' THEN 'Far Exceeds Expectations'
ELSE 'Empty')
END)
END) AS review_rating




"Nicholas"
Post #1411834
Posted Friday, January 25, 2013 11:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:50 PM
Points: 39, Visits: 274
Solved:

(CASE WHEN YEAR(H.effdt) IN ('2001', '2002') THEN
(CASE H.review_rating
WHEN '1' THEN 'Exceeded Way, Exceeded Results'
WHEN '2' THEN 'Exceeded Way, Achieved Results'
WHEN '3' THEN 'Exceeded Way, Did not meet Results'
WHEN '4' THEN 'Achieved Way, Did not meet Results'
WHEN '5' THEN 'Achieved Way, Achieved Results'
WHEN '6' THEN 'Achieved Way, Exceeded Results'
WHEN '7' THEN 'Did not meet Way, Exceeded Results'
WHEN '8' THEN 'Did not meet Way, Achieved Results'
WHEN '9' THEN 'Did not meet Way, Did not meet Results'
ELSE 'Empty'
END)
WHEN YEAR(H.effdt) IN ('2003','2004','2005') THEN
(CASE H.review_rating
WHEN '1' THEN 'Does not Meet Expectations'
WHEN '2' THEN 'Partially Meets Expectations'
WHEN '3' THEN 'Meets Expectations'
WHEN '4' THEN 'Exceeds Expectations'
WHEN '5' THEN 'Far Exceeds Expectations'
ELSE 'Empty'
END)
END) AS review_rating



"Nicholas"
Post #1411866
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse