case statement

Question

Post reply

case statement

sql4us

Old Hand

Points: 388
More actions
September 26, 2011 at 10:10 am

#144314

Hi,

I used case statement shown below, but it gives me wrong values(see result)..do i need to cast it? Any help is appreciated

CASE

WHEN K.[test] = 'N/A' THEN '0'

WHEN ((K.[test]>='0.0') and (K.[test]<'20.0')) THEN '1'

WHEN ((K.[test]>='20.0')and (K.[test]<'35.0')) THEN '2'

WHEN (K.[test]>='35.0') THEN '3'

ELSE ' '

END AS [Key]

Result:

test Key

N/A 0

6.5 3 // supposed to be 1, showing as 3

8.6 3

2.1 1

0.0 1

8.3 3

60.0 3

3.6 2

4.2 3

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

you'll need to cast or convert; you cannot compare strings like that; you run into trouble with greater than and between and stuff not behaving the way the value of it would.



With K (test,[Key])

AS

(SELECT 'N/A','0' UNION ALL

SELECT '6.5','3' UNION ALL

SELECT '8.6','3' UNION ALL

SELECT '2.1','1' UNION ALL

SELECT '0.0','1' UNION ALL

SELECT '8.3','3' UNION ALL

SELECT '60.0','3' UNION ALL

SELECT '3.6','2' UNION ALL

SELECT '4.2','3')

SELECT K.* ,

CASE

WHEN K.[test] = 'N/A' THEN '0'

WHEN ((CONVERT(decimal(19,2),K.[test])>=0.0) and (CONVERT(decimal(19,2),K.[test])<20.0)) THEN '1'

WHEN ((CONVERT(decimal(19,2),K.[test])>=20.0)and (CONVERT(decimal(19,2),K.[test])<35.0)) THEN '2'

WHEN (CONVERT(decimal(19,2),K.[test])>=35.0) THEN '3'

ELSE ' '

END AS [Key]

FROM K

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql4us Old Hand Points: 388 More actions · Answer 2

Thanks for the reply. I tried cast and also convert as you mentioned (below code), it throws me an error

"Error converting data type varchar to numeric."

CASE

WHEN K.[test] = 'N/A' THEN '0'

WHEN ((CONVERT(decimal(19,2),K.[test])>=0.0) and (CONVERT(decimal(19,2),K.[test])<20.0)) THEN '1'

WHEN ((CONVERT(decimal(19,2),K.[test])>=20.0)and (CONVERT(decimal(19,2),K.[test])<35.0)) THEN '2'

WHEN (CONVERT(decimal(19,2),K.[test])>=35.0) THEN '3'

END AS [Key]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SELECT K.[test],



	CASE

	WHEN K.[test] = 'N/A' THEN '0'

	WHEN CAST(K.[test] AS DECIMAL(5,1)) BETWEEN 0 AND 19.9 THEN '1'

	WHEN CAST(K.[test] AS DECIMAL(5,1)) BETWEEN 20 AND 34.9 THEN '2'

	WHEN CAST(K.[test] AS DECIMAL(5,1)) > 34.9 THEN '3'

	ELSE ' '

	END AS [Key]

FROM ( -- sample data

	SELECT [test] = CAST('-1' AS VARCHAR(5)) UNION ALL

	SELECT '0' UNION ALL

	SELECT '0.1' UNION ALL

	SELECT '1' UNION ALL

	SELECT '19' UNION ALL

	SELECT '19.9' UNION ALL

	SELECT '20' UNION ALL

	SELECT '20.1' UNION ALL

	SELECT '34.8' UNION ALL

	SELECT '34.9' UNION ALL

	SELECT '35' UNION ALL

	SELECT '35.1' UNION ALL

	SELECT NULL

) K

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lowell SSC Guru Points: 323482 More actions · Answer 4

sql4us (9/26/2011)

Thanks for the reply. I tried cast and also convert as you mentioned (below code), it throws me an error

"Error converting data type varchar to numeric."

besides 'N/A' it looks like you have other vbalues(empty string)? in the data; you'll have to take those non-isnumeric values into consideration in your CASE statement also.

maybe this?



SELECT K.* ,

CASE

WHEN K.[test] = 'N/A' THEN '0'

WHEN ISNUMERIC(K.[test]) = 0 THEN '0'

WHEN ((CONVERT(decimal(19,2),K.[test])>=0.0) and (CONVERT(decimal(19,2),K.[test])<20.0)) THEN '1'

WHEN ((CONVERT(decimal(19,2),K.[test])>=20.0)and (CONVERT(decimal(19,2),K.[test])<35.0)) THEN '2'

WHEN (CONVERT(decimal(19,2),K.[test])>=35.0) THEN '3'

ELSE ' '

END AS [Key]

FROM K

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql4us Old Hand Points: 388 More actions · Answer 5

Lowell (9/26/2011)

sql4us (9/26/2011)

Thanks for the reply. I tried cast and also convert as you mentioned (below code), it throws me an error

"Error converting data type varchar to numeric."

besides 'N/A' it looks like you have other vbalues(empty string)? in the data; you'll have to take those non-isnumeric values into consideration in your CASE statement also.

maybe this?

SELECT K.* , CASE WHEN K.[test] = 'N/A' THEN '0' WHEN ISNUMERIC(K.[test]) = 0 THEN '0' WHEN ((CONVERT(decimal(19,2),K.[test])>=0.0) and (CONVERT(decimal(19,2),K.[test])<20.0)) THEN '1' WHEN ((CONVERT(decimal(19,2),K.[test])>=20.0)and (CONVERT(decimal(19,2),K.[test])<35.0)) THEN '2' WHEN (CONVERT(decimal(19,2),K.[test])>=35.0) THEN '3' ELSE ' ' END AS [Key] FROM K

Thanks so much for the reply. I tried your case statement, all key values shows "0". I dont have an empty string in my data..not sure why its not showing the exact values after converting to decimal...

sql4us Old Hand Points: 388 More actions · Answer 6

ChrisM@Work (9/26/2011)

SELECT K.[test], CASE WHEN K.[test] = 'N/A' THEN '0' WHEN CAST(K.[test] AS DECIMAL(5,1)) BETWEEN 0 AND 19.9 THEN '1' WHEN CAST(K.[test] AS DECIMAL(5,1)) BETWEEN 20 AND 34.9 THEN '2' WHEN CAST(K.[test] AS DECIMAL(5,1)) > 34.9 THEN '3' ELSE ' ' END AS [Key] FROM ( -- sample data SELECT [test] = CAST('-1' AS VARCHAR(5)) UNION ALL SELECT '0' UNION ALL SELECT '0.1' UNION ALL SELECT '1' UNION ALL SELECT '19' UNION ALL SELECT '19.9' UNION ALL SELECT '20' UNION ALL SELECT '20.1' UNION ALL SELECT '34.8' UNION ALL SELECT '34.9' UNION ALL SELECT '35' UNION ALL SELECT '35.1' UNION ALL SELECT NULL ) K

Thanks chris for the reply.

It works with your sample data but when I applied the case statement to my data it shows an error:"

Msg 8114, Level 16, State 5, Line 3

Error converting data type varchar to numeric."

Lowell SSC Guru Points: 323482 More actions · Answer 7

try something like this to track down the non numeric values:



SELECT DISTINCT '>' + [test] + '<' FROM K

WHERE ISNUMERIC([test]) = 0

maybe it's a value like 'NA" or 'N / A'...but that's what i'd think when you get that cannot convert error.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql4us Old Hand Points: 388 More actions · Answer 8

sql4us

Old Hand

Points: 388

September 26, 2011 at 11:02 am

#1386676

Thank you so much , it worked 🙂 I had '%' in my query to represent the values as percentage, because of that it was causing an issue, when I removed '%' from the query it worked 🙂

Is there anywhere in this forum where I can mark as "answer" or something like that?

sql4us Old Hand Points: 388 More actions · Answer 9

sql4us

Old Hand

Points: 388

September 26, 2011 at 11:03 am

#1386677

Thanks a lot, your case statement also worked 🙂

sql4us Old Hand Points: 388 More actions · Answer 10

Thanks your case statement also worked 🙂

ChrisM@Work (9/26/2011)

SELECT K.[test], CASE WHEN K.[test] = 'N/A' THEN '0' WHEN CAST(K.[test] AS DECIMAL(5,1)) BETWEEN 0 AND 19.9 THEN '1' WHEN CAST(K.[test] AS DECIMAL(5,1)) BETWEEN 20 AND 34.9 THEN '2' WHEN CAST(K.[test] AS DECIMAL(5,1)) > 34.9 THEN '3' ELSE ' ' END AS [Key] FROM ( -- sample data SELECT [test] = CAST('-1' AS VARCHAR(5)) UNION ALL SELECT '0' UNION ALL SELECT '0.1' UNION ALL SELECT '1' UNION ALL SELECT '19' UNION ALL SELECT '19.9' UNION ALL SELECT '20' UNION ALL SELECT '20.1' UNION ALL SELECT '34.8' UNION ALL SELECT '34.9' UNION ALL SELECT '35' UNION ALL SELECT '35.1' UNION ALL SELECT NULL ) K