Hi,
I am wanting some advice on the best way to deal with NULLS. Basically want to get ride of/translate them to avoid all the problems with reporting writing against them e.g. appends
By way of an example I am reporting against a incident logging tool's database and field's such as category/department/priority are NULL at various points in the incident's lifecycle.
I would like a simple way to say for a particular field that when NULL replace with an empty string or 0. At the moment it seems my options are as follows:
1] wrap the table in a virtual table that uses a CASE or ISNULL statement for the relevant columns
2] on the view wrap the field in a calculated field that does the same
Both of these are fairly painful and make changes a drag as well. Would it be possible to write a custom function that essentially mimics ISNULL and casts the field to a 0 or '' if null?
Or am I missing an easier way to accomplish the above.
thanks
Robin
unless you are using cross tab reports (in which there is an option to set NULLs as zeros) there is no easier way than what you've listed. By far the easiest would be the Custom Function, and there's no need to mimick the ISNULL function, just use the actual function! I tested it out and it works fine:
[code]
IsNull
1
column 1
numeric
isnull($1, 0)
isnull($1, 0)
SQLServer
numeric
[/code]
this is for SQL Server, so you may have to change the syntax depending on what your database is. Also, again depending on what your database is you may have to use the tag.
regards,
Dave