Re: Coalesce() in Informix SE
Martyn Shiner wrote:
> 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?
Guessing that the imextras.addncost field is likely to be a decimal or
money column, maybe:
-- untested code
create procedure coalesce(d1 decimal, d2 decimal default null)
returning decimal;define d0 decimal;
if (d1 is not null) then let d0 = d1;
elsif (d2 is not null) then let d0 = d2;
else let d0 = null;
end if;
return d0;
end procedure;
SE does not support NVL or COALESCE or CASE or DECODE. It does
support stored procedures, so some variant on the above is reasonable
- and you can extend the argument list with defaulted variables if you
wish - as for DECODE. In practice, I'd probably use VARCHAR in place
of DECIMAL; it will work correctly for any type up to 255 bytes long.
Oops; on second thoughts - SE doesn't support VARCHAR either. Make
that CHAR(255) then - or some other shorter length to suit (eg 32).
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/