Replacing NAN values with nothing or a zero

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 values with nothing or a zero

Author

Topic

gundi
Starting Member

1 Post

Posted - 2011-11-29 : 03:30:05

Hi I have the following:
=Round((SUM(IIF(Fields!Flag.Value = "N", CDbl(Fields!Figure.Value),CDbl(0)))
* 100)
/SUM(IIF(Fields!Flag.Value = "T",CDbl(Fields!Figure.Value),CDbl(0)))

The problem is if there are no values it return NAN. I have tryed to use Nothing but it returns an error. I am sure I am putting it in a wrong way.

Can somebody assist?

Regards
Gundi

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-11-29 : 08:33:22

Hi
Try this code

=Round((SUM(IIF(Nz(Fields!Flag.VALUE,"") = "N", CDbl(Nz(Fields!Figure.VALUE,0)),CDbl(0)))
* 100)
/SUM(IIF(Nz(Fields!Flag.VALUE,"") = "T",CDbl(Nz(Fields!Figure.VALUE,0)),CDbl(0)))

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources