Re: Coalesce() in Informix SE
See Jonathan's post - seems SE does not support CASE
"Mark Denham" <mkdenham@comcast.net> wrote in message
news:bg4qc4$1m3$1@terabinaries.xmission.com...
>
> 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