Replacing NaN with 0 or blank - 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 2008 Forums

Transact-SQL (2008)

Replacing NaN with 0 or blank

Author

Topic

lee_van_cleef
Starting Member

2 Posts

Posted - 2013-03-18 : 11:05:59

Hello,

I have the following:

=(Sum( IIF( Fields!YEAR.value = 2013, Fields!Number.Value, nothing)) - Sum( IIF( Fields!YEAR.value = 2012, Fields!Number.Value, nothing)) ) / Sum( IIF( Fields!YEAR.value = 2013, Fields!Number.Value, nothing))

I'm trying to find the difference over those years however the problem is when there are no values it returns NaN. Or if there are values for 2012 and none for 2013 it returns error or infinity.

How do I change this to show as either 0 or preferably be blank

Thanks
Lee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-18 : 12:57:13

use IsNan() function to check for NaN values. its used as Double.Nan() in SSRS

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-19 : 10:23:59

can you show your current used query?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources