Error in 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)

Error in CASE statement

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2009-01-30 : 03:03:34

Hi,

I am comparing two values using CASE.
One of them is VARCHAR and the other is NUMERIC.
This works but sometimes it is giving error. I am not able to recoginse the error. I tried converting one to them to the other format,but still it didnt work.
here is the query:

DECLARE PlannedEffort varchar(10)
SET PlannedEffort = '10'
DECLARE ActualEffort numeric(32,2)
SET ActualEffort = 2.00

select
case when ActualEffort > PlannedEffort then 'Yes' else 'No' end as
"TimeExceeded"

Please advice...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-30 : 03:12:15

What is the error message?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 03:33:09

it should be this


DECLARE @PlannedEffort varchar(10)
SET @PlannedEffort = '10'
DECLARE @ActualEffort numeric(32,2)
SET @ActualEffort = 2.00

select 
case when @ActualEffort > @PlannedEffort then 'Yes' else 'No' end as 
'TimeExceeded'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 03:33:46

quote:
Originally posted by bklr

how it will compare the varchar and numeric u will get the error as Error converting data type nvarchar to numeric.

if plannedeffort is numbervalue only then convert actualeffort value to varchar

nope. you wont as per values given. its still numeric data and will undergo implicit conversion

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-01-30 : 03:46:56

Try this,

select
case when CAST(ActualEffort AS INT) > CAST(PlannedEffort AS INT ) then 'Yes' else 'No' end as TimeExceeded

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

mrm23
Posting Yak Master

198 Posts

Posted - 2009-01-30 : 05:23:30

Hi All,
Thanks a lot for ur replies.

Rajesh,
Thanks a lot. it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources