September 26, 2011 at 10:10 am
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
September 26, 2011 at 10:16 am
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
September 26, 2011 at 10:24 am
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]
September 26, 2011 at 10:27 am
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
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
September 26, 2011 at 10:31 am
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
September 26, 2011 at 10:48 am
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...
September 26, 2011 at 10:51 am
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."
September 26, 2011 at 10:52 am
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
September 26, 2011 at 11:02 am
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?
September 26, 2011 at 11:03 am
Thanks a lot, your case statement also worked 🙂
September 26, 2011 at 11:03 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply