Re: Convert Nulls in Select statement
Paul Yurga <pyurga@lucent.com> wrote:
:Is there a way to have a null field converted to a default value (say 0
:for an int) in a SQL statement? Sybase had a coerse statement I believe
:that could do this. I need non null values to do computations with
:later, but the table allows nulls by default so I have to deal with them
:also. A simple function that converts a null to a default value on the
:fly would be fine if I could figure out how to do it. I'm new to
:Informix so any help would be appreciated.
If some more of sql92 had been implemented a case statement in the
select list could have easily done this. That's not yet there though.
You can however do it with a simple stored procedure. Simply crate one
for each datatype something like:
create procedure nullint(p_int integer)
returning integer;
if p_int is null then
return 0; -- or whatever you want
else
return p_int;
end if;
end procedure
and then do a select like:
select nullint(intcolumn), othercolumns...
from whatevertable
...
You might also want to do:
select nullint(intcolumn) as somename, othercolumns...
from whatevertable
...
to name the column returned through the stored procedure.
I have *not* tested the above, so you may get all kinds of errors.
Just trie it out and read the manuals.
Nils.Myklebust@idg.no
NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org