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 2000 Forums
 SQL Server Development (2000)
 ISNULL for all values

Author  Topic 

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2005-07-26 : 18:11:24
Is there a way to return 0 for all values that are null without using isnull on every single column in the select statement? Something to the effect of:
SELECT ISNULL((col1,col2,col3,col4,col5),0) FROM table

Sorry if this has been posted before, I didn't see anything like this when I searched.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-26 : 18:17:19
No. You'll need to use ISNULL for each column. If that is unacceptable, handle this in the presentation layer.

Tara
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-26 : 18:33:49
Next time You use NULL in the database,
ask Yourself if the column really needs to be nullable.

If possible, update all NULL columns to 0, and change them to NOT NULL.
Might be a permanent solution.

rockmoose
Go to Top of Page

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2005-07-26 : 18:41:38
Thanks Tara and rockmoose, I don't know how to tell SQL Reporting to convert NULLs to zeros, and the columns are created from SUMs and JOINs, so NOT NULL won't help in this case.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-26 : 18:50:01
Here's how to do it in Reporting Services:

IIf((Len(Fields!SomeField.Value)>0), Fields!SomeField.Value, 0)

This is how to deal with NULL data in RS. Len(Fields!SomeField.Value) will be greater than 0 if the data is not NULL. Otherwise, it's NULL.

Tara
Go to Top of Page

mmaglala
Starting Member

1 Post

Posted - 2009-12-04 : 14:28:06
it's better to use IsNothing versus LEN in reporting services.
Go to Top of Page
   

- Advertisement -