Hi Martijn,

> Arno,
>
> > > SELECT Code, SUM(CASE Amount WHEN NULL THEN 0 else Amount) from TableA
> > >
> > > But this is not working. I tried to use IF..THEN..ELSE But it was also
> > failure for me.
> >
> > In Firebird 1.5 you can do:
> >
> > SELECT
> > Code,
> > CASE WHEN SUM(Amount) IS NULL THEN 0 ELSE Amount END
> > FROM
> > TableA
>
> Mind you - the result of the above query is different that what might
> be the intention of the first attempt.
>
> You're transforming SUM = NULL to 0, while his first attempt was
> to SUM x, but when x = NULL, then x = 0...

Yes, but it is more efficient to do it after the SUM else is for every value
also the CASE/COALESCE evaluated and in my example only afterwards. The SUM
doesn't sum NULL values, they are already ignored, but the result off SUM
can be NULL :-)

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81