[Switters]

I'm using MS Sql Server 2005. It has been awhile since I've needed to write case statements but I didn't think I had forgotten that much. I have a table with a bunch of "rank" codes corresponding to individual line items, and I need to decompose these ranks (currently varchar50) into integers. So I built this statement within a view:

CASE
WHEN theRank = 'A1' THEN 1
WHEN theRank = 'A2' THEN 2
WHEN theRank = 'A3' THEN 3
WHEN theRank = 'A4' THEN 4
WHEN theRank = 'A5' THEN 5
WHEN theRank = 'A6' THEN 6
WHEN theRank = 'A7' THEN 7
WHEN theRank = 'A8' THEN 8
WHEN theRank = 'A9' THEN 9
WHEN SUBSTRING(theRank,1,1) = 'O' THEN 10
ELSE 0
END AS theRankN,

The problem is that on any number of the individual records, the value assigned is flat-out wrong. For example, I tested the substring function completely standalone, and it correctly pulls out the 'O' But then the statement assigns a 4. Similarly, 'A4' sometimes returns a 10.

Can anyone provide some ideas on what I've got wrong?

[maxrock]

Tried it and it works for me!

I also came up with a simplification.

Hope it is helpful.

SELECT CASE WHEN LEFT(theRank,1) = 'A' THEN RIGHT(theRank,1)
WHEN LEFT(theRank,1) = 'O' THEN 10
ELSE 0 
END
AS theRankN

Regards,

[Switters]

maxrock, on 19 February 2013 - 06:47 AM, said:

Tried it and it works for me!

I also came up with a simplification.

Hope it is helpful.

SELECT CASE WHEN LEFT(theRank,1) = 'A' THEN RIGHT(theRank,1)
WHEN LEFT(theRank,1) = 'O' THEN 10
ELSE 0 
END
AS theRankN

Regards,

Thanks for posting a response. As you say, the code actually does work (and I like your simplified version). The issue is that I was using the "open view" tool within object explorer, but I forgot that views do not always materialize properly. When I actually exported the view (and re-tested this by selecting it into a table) with DTSWizard, I did a field-level comparison and the statements actually work as intended.

I spent way too much time pulling my hair out over this one but I will never trust "open view" again.