case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

case statement

Author

Topic

sql4us
Starting Member

24 Posts

Posted - 2011-09-26 : 12:05:33

Hi,

I used case statement shown below, but it gives me wrong values..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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 12:09:31

thats because you're comparing them as varchar values. why not do integer comparison?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-26 : 12:14:48

Also why are you mixing varchar values with decimals?

Madhivanan

Failing to plan is Planning to fail

sql4us
Starting Member

24 Posts

Posted - 2011-09-26 : 12:18:20

quote:
Originally posted by madhivanan

Also why are you mixing varchar values with decimals?

Madhivanan

Failing to plan is Planning to fail

My results(test) are in decimals (with %) , so I need to assign key values for each of those based on the range..

sql4us
Starting Member

24 Posts

Posted - 2011-09-26 : 12:19:44

quote:
Originally posted by visakh16

thats because you're comparing them as varchar values. why not do integer comparison?

Thanks for the reply. My results are in decimal values, is there any sample to show the comparison in integer?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 12:21:50

quote:
Originally posted by sql4us

quote:
Originally posted by madhivanan

Also why are you mixing varchar values with decimals?

Madhivanan

Failing to plan is Planning to fail

My results(test) are in decimals (with %) , so I need to assign key values for each of those based on the range..

why storing them with % signs? why cant you store them as numeric values? you can always add formatting (%) at front end application. else you're making the manipulations using values difficult just like what you've experienced

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 12:26:36

or else you've do a lot of casts like below


SELECT 
.....
CASE 
WHEN K.[test] = 'N/A' THEN 0
WHEN ((K.[test]*1>=0.0) and (K.[test]*1<20.0)) THEN 1
WHEN ((K.[test]*1>=20.0)and (K.[test]<35.0)) THEN 2
WHEN (K.[test]*1>=35.0) THEN 3

ELSE NULL
END AS [Key].

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql4us
Starting Member

24 Posts

Posted - 2011-09-26 : 12:27:28

quote:
Originally posted by visakh16

quote:
Originally posted by sql4us

quote:
Originally posted by madhivanan

Also why are you mixing varchar values with decimals?

Madhivanan

Failing to plan is Planning to fail

My results(test) are in decimals (with %) , so I need to assign key values for each of those based on the range..

why storing them with % signs? why cant you store them as numeric values? you can always add formatting (%) at front end application. else you're making the manipulations using values difficult just like what you've experienced

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yep, I did have the % at the front end application, its just the values are in decimals and when I did converted to decimal it still throwing me an error..

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]

Error:Error converting data type varchar to numeric.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 13:04:12

thats because you've non numeric values also(N/A) convert it to something else and then try like


SELECT CASE 
WHEN K.[test] IS NULL 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],...
FROM 
(
SELECT NULLIF(test,'N/A') AS test
...

)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources