Re: Coalesce() in Informix SE
Have you tried the decode function:
decode(NULL, 0, imextras.addncost)
Not sure if your version supports this or not.
You could also look at CASE.
select case when imextras.addncost is null
then 0
else imextras.addncost
end,
Not sure if I have the syntax 100% correct for case.
Mark
----- Original Message -----
From: "Martyn Shiner" <nospammartyn.jshiner@virgin.net>
To: <informix-list@iiug.org>
Sent: Monday, July 28, 2003 16:02
Subject: Coalesce() in Informix SE
> I have a query that needs to add two columns, one of which may contain a
> null (as a result of an outer join) - obviously the null propagates which
> causes me problems. My docs suggest that SE doesn't support the ANSI
> coalesce() function - is there a workaround?
>
> Query is as follows:
>
> SELECT imast.item, (imast.cost+imextras.addncost) itemcost
> FROM imast outer imextras
> WHERE imast.item=imextras.item>
> I want to write:
> SELECT imast.item itemcode, (imast.cost+coalesce(imextras.addncost,0))
> itemcost
> FROM imast outer imextras
> WHERE imast.item=imextras.item
>
> Any ideas?
> Regards
>
> Martyn
>
>
sending to informix-list