Coalesce() in Informix SE

Discussion:

(too old to reply)

preetinder dhaliwal

2003-07-28 22:26:09 UTC

does nvl work? - imast.cost+nvl(imextras.addncost,0)

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?
SELECT imast.item, (imast.cost+imextras.addncost) itemcost
FROM imast outer imextras
WHERE imast.item=imextras.item
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

Mark Denham

2003-07-29 03:01:45 UTC

Permalink

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" <***@virgin.net>
To: <informix-***@iiug.org>
Sent: Monday, July 28, 2003 16:02
Subject: Coalesce() in Informix SE

sending to informix-list

Martyn Shiner

2003-07-29 19:46:08 UTC

Permalink

See Jonathan's post - seems SE does not support CASE

Post by Mark Denham
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 -----
Sent: Monday, July 28, 2003 16:02
Subject: Coalesce() in Informix SE

sending to informix-list