SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Error in CASE statement		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrm23
Posting Yak Master

India
198 Posts
Posted - 01/30/2009 :  03:03:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 01/30/2009 :  03:12:15  Show Profile  Visit webfred's Homepage  Reply with Quote
What is the error message?

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

visakh16
Very Important crosS Applying yaK Herder

India
48012 Posts
Posted - 01/30/2009 :  03:33:09  Show Profile  Reply with Quote
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'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48012 Posts
Posted - 01/30/2009 :  03:33:46  Show Profile  Reply with Quote
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
Go to Top of Page

Rajesh Jonnalagadda
Starting Member

India
45 Posts
Posted - 01/30/2009 :  03:46:56  Show Profile  Visit Rajesh Jonnalagadda's Homepage  Reply with Quote
Try this,

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


Rajesh Jonnalagadda
GGK TECH
Go to Top of Page

mrm23
Posting Yak Master

India
198 Posts
Posted - 01/30/2009 :  05:23:30  Show Profile  Reply with Quote
Hi All,
Thanks a lot for ur replies.

Rajesh,
Thanks a lot. it works.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000