Null v/s empty string or blank or nothin" or space(s)
464664Dec 13 2007 — edited Dec 14 2007We've converted some DB2 data into our Oracle reporting database. Our reporting end-users have asked us to convert null values to blank or space(s). This is due to they do some reporting from Excell froma apparently it is said to be difficult to handle nulls. I think it is mostly a knowledge issue, so i guess better to make things simple for them. Due to nulls existing in a specific column listed in their where clause, they return calculations baded on 73 records v/s 101 in DB2. This due b/c you can't compare a null against a string value. My question is: Is it better to store null and use views to handle the nulls for them or convert the nulls to empty strings / space.?
1) create view ... (...null_column,...) as select ...nvl(null_column,' ') from ...;
2) update ... set null_column = ' ' where null_column is null;
Personally, my opinion is to leave it as null and use views to handle it. A null is a null, you know what it is. Once converted to space, it isn't readily apparent which columns have "null" values. I feel like converting the nulls to space(s), is like having garbage in the database. I did several goolge searches, checked metalink, and checked here, but didn't find any real useful or definitive thoughts or guidelines on the matter. Your thoughts on this are appreciated.