CASE statement: error converting numeric

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

General SQL Server Forums

New to SQL Server Programming

CASE statement: error converting numeric

Author

Topic

spitfire122
Starting Member

11 Posts

Posted - 2011-06-15 : 11:47:14

follow-up case statement to the one i just had... now i want to know the difference between... but ls.principal_balance is a numeric and cma_as_is_value is varchar... i tried the CONVERT(numeric function but it will not work...

,case when (ls.principal_balance - msp3.cma_as_is_value) > 1 and msp3.cma_as_is_value is not null then ls.principal_balance - msp3.cma_as_is_value
	  when (ls.principal_balance - ls.purchase_value) > 1 then ls.principal_balance - ls.purchase_value
	else '' end as Negative_Equity_Amount

error: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-15 : 12:03:51

It seems you've got data that can't be converted to numeric/decimal. Why do you have non-numeric data in there?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-15 : 12:09:13

Take a look at your ELSE clause; it is a string. Strings are higher than numeric values; this whole thing has to return a string! If you quit thinking a CASE expression is a statement, you will not have so many problems.

Is this more like what you wanted:

CASE WHEN (LS.principal_balance - msp3.cma_as_is_value) > 1.00
AND msp3.cma_as_is_value IS NOT NULL
THEN LS.principal_balance - msp3.cma_as_is_value
WHEN (LS.principal_balance - LS.purchase_value) > 1.00
THEN LS.principal_balance - LS.purchase_value
ELSE 0.00 END AS negative_equity_amount

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources