[andrewhallam]

Anyone know of a way to use CASE with SUM and NOT NULL in an INFORMIX SQL SELECT statement?

The below SQL works fine in SQL SERVER and DB2 but INFORMIX doesn't like it. (For info - there are no rows matching on bom for bomparent = 'XXX')


SELECT CASE WHEN SUM(bomweight) IS NULL THEN 0 ELSE SUM(bomweight) END AS weight
FROM bom WHERE bomparent = 'XXX'

Error: IS [NOT] NULL predicate may be used only with simple columns. (State1000, Native Code: FFFFFEDB)

Now I could use the ANSI standard COALESCE() function (similar to SQL SERVER ISNULL()) to obtain the same result but again unfortunately INFORMIX does not support this either.

SELECT COALESCE(SUM(bomweight), 0) AS weight
FROM bom WHERE bomparent = 'XXX'

-Under SQLSERVER and DB2 the result is one row:
WEIGHT
0.0

Thanks in advance for your help on this.

Andy
ahmatexeldotcodotuk

[ceinma]

use the funciont NVL

SELECT NVL(SUM(bomweight) ,0) AS weight
FROM bom WHERE bomparent = 'XXX'

But this can retreive undesirable result ..

the corret way to use is:

SELECT SUM(NVL(bomweight,0)) AS weight
FROM bom WHERE bomparent = 'XXX'

[andrewhallam]

Thanks for that - your a star.

Cheers.

[r937]

ceinma, what "undesirable result" does NVL(SUM(bomweight),0) produce?

[ceinma]

on reality, with sum and any other aggregate function the NULLs values are ignored...so, works too. But just to a good practice must avoid this kind of code... my opinion..