2 Replies - 2015 Views - Last Post: 19 February 2013 - 09:11 AM Rate Topic: -----

#1 Switters  Icon User is offline

  • D.I.C Head

Reputation: 25
  • View blog
  • Posts: 110
  • Joined: 03-June 12

SQL Case statement not working properly

Posted 18 February 2013 - 11:46 PM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Case statement not working properly

#2 maxrock  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 70
  • Joined: 13-September 09

Re: SQL Case statement not working properly

Posted 19 February 2013 - 06:47 AM

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,
Was This Post Helpful? 1
  • +
  • -

#3 Switters  Icon User is offline

  • D.I.C Head

Reputation: 25
  • View blog
  • Posts: 110
  • Joined: 03-June 12

Re: SQL Case statement not working properly

Posted 19 February 2013 - 09:11 AM

View Postmaxrock, 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1